Sort and find position in string - Qlik Community

jdmarlin · ‎2016-06-23

I have a formula that concatenates a field MATURITY_BUCKET:

Concat(DISTINCT MATURITY_BUCKET, ';', MATURITY_BUCKET)

And produces this:

0-5 Days;1-2 Years;15-30 Days;180-365 Days;30-45 Days;45-60 Days;5-15 Days;60-75 Days;75-90 Days;90-180 Days

I'm looking to:

1) Sort the string so that it's in order:

0-5 Days;5-15 Days;15-30 Days;30-45 Days;45-60 Days;60-75 Days;75-90 Days;90-180 Days;180-365 Days;1-2 Years

2) Return 0 if string is '0-5 Days' and 1 if string is '5-15 Days', and etc..

trdandamudi · ‎2016-06-23

May be as attached:

Create a table as shown in the attached file and join to your table in the model. Doing this you will get the maturity bucket in order.

When you select 0-5 Days in the Maturity Bucket you will see 1 in the text box.

Hope this helps..

rwunderlich · ‎2016-06-23

You can either establish the sort order in the script (I think easiest) as shown here:

Or use a match() for sort weight:

Concat(DISTINCT MATURITY_BUCKET, ';',

match(MATURITY_BUCKET,

'0-5 Days','5-15 Days','180-365 Days','1-2 Years'

)

I'm not going to type them all out but I'm sure you get the idea.

-Rob