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

Pavan Cheruvu February 21, 2024

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

3 answers

3 accepted

0 votes
Answer accepted
Stepan Kholodov _Tempo_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 21, 2024

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 February 21, 2024

Thanks very much Stepan @Stepan Kholodov _Tempo_ 

The code  you shared, works !

Regards, Pavan

 

Like Stepan Kholodov _Tempo_ likes this
pavan_cheruvu February 29, 2024

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

0 votes
Answer accepted
Radek Dostál
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 21, 2024

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 February 21, 2024

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

0 votes
Answer accepted
Humashankar VJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 21, 2024

This one is interesting to solve

Suggest an answer

Log in or Sign up to answer