Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Define ranges within a numeric field

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

10 Replies
awhitfield
Esteemed Contributor

Re: Define ranges within a numeric field

hansdevr
Contributor II

Re: Define ranges within a numeric field

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

MVP
MVP

Re: Define ranges within a numeric field

maybe with an interval match, something like this

Re: Extend variable classes

hansdevr
Contributor II

Re: Define ranges within a numeric field

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

MVP
MVP

Re: Define ranges within a numeric field

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
Contributor II

Re: Define ranges within a numeric field

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?

MVP
MVP

Re: Define ranges within a numeric field

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
Contributor II

Re: Define ranges within a numeric field

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

maybe helpful:

Custom Filter(unusual range)

regards

Marco