topic Sort and find position in string in QlikView

Sort and find position in string

jdmarlin — Thu, 23 Jun 2016 19:37:40 GMT

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 — Thu, 23 Jun 2016 21:25:49 GMT

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 — Fri, 24 Jun 2016 01:22:19 GMT

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