How do I use regex to convert number to string in a formula column of a structure board?

[Pavan Cheruvu]

Hi : This is the scenario, where I need help. I am using a formula column to sort and pick the latest fixversion.  The fixVersion are stored as "YY.MM.DD" and can have multiple values.

I am taking the strings in fixVersion and converting to a number, by removing the decimal. 

I than sort and pick the highest value. 

----------------

fixVersions

  .map(v -> v.name.replace(".", ""))

  .sortBy(v -> -v)

  .first()

-------------------------------

I now want to add the decimal back to the highest value, before displaying in the structure board.

I have tried various combinations like
.map(v -> v.replace(("\\d{2})(\\d{2})(\\d{2})", "$1.$2.$3"))

but it is not working!

Regards, Pavan

[Stepan Kholodov _Tempo_ · Accepted solution]

Hello @Pavan Cheruvu 

You can try this formula:

fixVersions .map(v -> ARRAY(v, v.name.replace(".", ""))) .sortBy(v -> -v.GET(1)) .first().GET(0)

I hope this helps. If you need help with anything else in Structure, please reach out to us directly at our support portal.

Best regards,
Stepan
Tempo (the Structure app vendor)

[Pavan Cheruvu]

Thanks very much Stepan @Stepan Kholodov _Tempo_ 

The code  you shared, works !

Regards, Pavan

[pavan_cheruvu]

Hi Stepan, @Stepan Kholodov _Tempo_ 

I request your help for a modification to the above.

At times, when the Fixed Version is not yet decided (early part of planning), the business user may enter free text : Eg "Yet to be decided" , "NA", "TBD" etc...there is no constraint on the free text.

How can I modify the above formula to return the above free text, when no Fixed Version value of format YY.MM.DD exists ? 

Regards, Pavan

[Radek Dostál · Accepted solution]

I don't follow.

I now want to add the decimal back to the highest value

You had a map to begin with, what happened to it?

fixVersions
  .map(v -> v.name.replace(".", ""))
  .sortBy(v -> -v)
  .first()

So you got a "YYMMDD" String from this. I assume the sort by value should work to get the latest, but you still have the same map you started with, no?

So when you later say

I have tried various combinations like
.map(v -> v.replace(("\\d{2})(\\d{2})(\\d{2})", "$1.$2.$3"))

Assuming you are doing this on the original map, the original map still has the same values it had in the YY.MM.DD format. So all you would need is to get the element from it.

This groovy syntax-like confuses me a bit so I don't know if exactly this would work

fixVersions
  .sortBy(v -> -v.name) // or (v -> -v.name.replace(".", ""))
  .first()

Thing is, I don't see the need to replace the dots. It should (unless groovy does something in the background) still sort it in the same order. If this was java, the whole sorting would still be happening on Strings, not numbers. I would expect groovy to do the same thing. So if it's only about sorting, you don't really need to replace anything.

Either way, what you're doing there at the end

.map(v -> v.replace(("\\d{2})(\\d{2})(\\d{2})", "$1.$2.$3"))

This would only work if:

• the map was already modified (which it wasn't from what I can see)
• replace is exact match, to use regex, you would need 'replaceAll(x, y)' which takes first param as regex
• dollar signs may need to be escaped, because groovy is probably treating them as GStrings by default, so maybe

\$ or \\$

may be needed

All in all though I'm working with a lot of leaps here without seeing more of the code.

[Pavan Cheruvu]

Hi Radek,

Thanks for your inputs. 

I will go thro' your inputs in detail.

Quick answer to you: I am trying get the decimals back to the YY.MM.DD, as that is the format the users are familiar with.

Regards, pavan

[Humashankar VJ · Accepted solution]

This one is interesting to solve