topic Re: Define ranges within a numeric field in QlikView

Define ranges within a numeric field

hansdevr — Tue, 25 Apr 2017 13:03:03 GMT

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?

Re: Define ranges within a numeric field

awhitfield — Tue, 25 Apr 2017 13:10:37 GMT

Class maybe?

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

Andy

Re: Define ranges within a numeric field

hansdevr — Tue, 25 Apr 2017 13:16:08 GMT

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).

Re: Define ranges within a numeric field

maxgro — Tue, 25 Apr 2017 13:16:11 GMT

maybe with an interval match, something like this

Re: Extend variable classes

Re: Define ranges within a numeric field

hansdevr — Tue, 25 Apr 2017 13:21:08 GMT

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...

Re: Define ranges within a numeric field

maxgro — Tue, 25 Apr 2017 13:39:56 GMT

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;

Re: Define ranges within a numeric field

hansdevr — Tue, 25 Apr 2017 13:44:08 GMT

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?

Re: Define ranges within a numeric field

maxgro — Tue, 25 Apr 2017 13:52:55 GMT

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

Re: Define ranges within a numeric field

hansdevr — Tue, 25 Apr 2017 13:57:40 GMT

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!!

Re: Define ranges within a numeric field

MarcoWedel — Tue, 25 Apr 2017 20:22:43 GMT

maybe helpful:

Custom Filter(unusual range)

regards

Marco

Re: Define ranges within a numeric field

hansdevr — Wed, 26 Apr 2017 05:51:22 GMT

Thnx Marco! Your solution looks a lot like Massimo's, BTW.

Still, suppose I have a table with some 500,000 records and I try an interval match to the ranges table - would the nested if be more slow than the match table? I will do the experiment today..