Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hansdevr
Creator III
Creator III

Define ranges within a numeric field

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?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

10 Replies
hansdevr
Creator III
Creator III
Author

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

maybe with an interval match, something like this

Re: Extend variable classes

hansdevr
Creator III
Creator III
Author

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

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

1.jpg

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
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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