Sort and find position in string

[jdmarlin]

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]

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]

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

Establishing a Sort Order | Qlikview Cookbook

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

http://masterssummit.com

http://qlikviewcookbook.com