Spotfire - Extract text based on conditions

Question

I have a column containing a string value as shown is the example below :

ZAE/GER-ERT/HEZ/PDC

The idea is to extract the first trigraph (ZAE in this extract) and a second one based a rule.

The rule is, if there is a '-' separating two trigraphs, we don't extract them, we just take the first trigraph after a '/' and without a '-' after it.

We then use a - to separate the two results, here is the aim for the example : ZAE-HEZ

I would like to get this value in a new calculated column.

I've tried to play with the indexes based on the Find() and ExtractRX() functions, but couldn't make it work.

Thanks in advance !

Answer 1

I am not sure this is the simplest way, but it works for your example (assuming the strings are always alphanumeric in chunks of 3).

You can do it via an intermediate column (for sanity, although you could put the [tmp] formula directly into the final column):

[tmp] as

RXReplace(RXReplace([your_column],'\\w{3}-\\w{3}','','g'),'/+','/','g')

This removes any double trigraph like GER-ERT and then removes any leftover double /

Then the final column splits [tmp] by / and concatenates the first and second item

Concatenate(Split([tmp],'/',1),'-',Split([tmp],'/',2))