Extract part of the string for max value within string

[Sagar_Apte]

Hi All,

I have Data as below.

LOAD * Inline [
ID, Text
1, "10-AB,20-CC,5-DD"
2, "25-CD,15-BD,10-AA,5-AG"
3, "30-TG,30-BG,20-KG" ];

Now, I need to extract text values for max number.  eg. For ID=1, max number is 20 so text associated with that number is "CC". If there is multiple max value then pick first occurrence. Format of text will remain the same with variable text values

Output:

CC

CD

TG

[Kushal_Chawda · Accepted solution]

@Sagar_Apte  try below

LOAD *,TextBetween(Text,evaluate('rangemax('&KeepChar(Text,'0123456789,')&')')&'-',',') as Max_String;
LOAD * Inline [
ID, Text
1, "10-AB,20-CC,5-DD"
2, "25-CD,15-BD,10-AA,5-AG"
3, "30-TG,30-BG,20-KG" ];

[Sagar_Apte]

@Kushal_Chawda  excellent. thank you