Define ranges within a numeric field

[hansdevr]

Hi everyone,

IMHO there has GOT to be a more clever way of doing this in stead of a nested 'if':

Anyone here found a more elegant trick to accomplish the same?

[awhitfield]

Class maybe?

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ConditionalFunctions/cl...

Andy

[hansdevr]

Hi Andy,

I had in fact been looking at class, but it doesn't quite get me that neat string with the €- sign and all that, does it?

Above that, below € 100 K the ranges differ (requirement from the business).

[maxgro]

maybe with an interval match, something like this

Re: Extend variable classes

[hansdevr]

No, that is not what I'm after. I need to get a new field which contains the ranges of the other field.

Class indeed comes close, but below € 100,000, the ranges get more refined...

[maxgro]

why not? look at the attachment, bucket is the new field

FactTable:

LOAD Ceil(rand()*1000000) as Tot_res_grp AutoGenerate 100;

tmpBuckets:

LOAD * INLINE [

START, END , bucket

0, 4999, '0-5000'

5000, 9999, '5000-10000'

10000,15000, '10000-15000'

15000,100000, '15000-100000'

100000,200000, '100000-200000'

200000,300000, '200000-300000'

300000,400000, '300000-400000'

400000,500000, '400000-500000'

500000,99999999,'>500000'

];

left join (FactTable)

IntervalMatch(Tot_res_grp)

Load START, END Resident tmpBuckets;

Left join (FactTable)

LOAD START, END, bucket

resident tmpBuckets;

DROP Table tmpBuckets;

[hansdevr]

Whoa, this looks a LOT like what I am after.. And it does indeed look elegant..

Two questions though:

- is this faster than my nested if?

- doesn't the interval match create a huge table?

[maxgro · Accepted solution]

And it does indeed look elegant..

Yes, elegant, but I don't know if it's faster than nested if.

You have to check with your data.

I would choose the if: simple to write, simple to understand

[hansdevr]

Nevertheless, I will do some experimenting with this. And you're right - the nested if IS simple to read.

Thnx very much for the effort you put into this, Massimo! Much appreciated!!

[MarcoWedel]

maybe helpful:

Custom Filter(unusual range)

regards

Marco