topic Re: Need an inverse to the Pick function in QlikView

Need an inverse to the Pick function

Mon, 19 Jan 2015 15:16:28 GMT

Hi All,

I'm building some fractile calculation into a tool, and while what I have currently works, it's not very fast. What I need is a function that essentially works as an inverse to a Pick function where there are a list of possible conditions, and it returns the place the first passing condition falls in.

The way I'm doing it currently is:

- Calculate all of the max values for each decile bucket:

vE_Decile_1_max = fractile($(vE_Decile_max_Calc),0.1)

vE_Decile_2_max = fractile($(vE_Decile_max_Calc),0.2)

vE_Decile_3_max = fractile($(vE_Decile_max_Calc),0.3)

etc.

Then running it through a big if statement:

if($(vE_PP_Total_Sales) <= $(vE_Decile_1_max), 1,

if($(vE_PP_Total_Sales) <= $(vE_Decile_2_max), 2,

if($(vE_PP_Total_Sales) <= $(vE_Decile_3_max), 3,

...

While this works, it's slow. I don't believe the class function will work either since the buckets aren't the same size.

Anyone have any ideas on how to speed this up?

Thanks!

Spencer

Re: Need an inverse to the Pick function

Mon, 19 Jan 2015 15:37:57 GMT

Try to use Switch Statment...

Vinay

Re: Need an inverse to the Pick function

Mon, 19 Jan 2015 15:46:39 GMT

I'm not doing this in the script but rather in the tool itself as it needs to be responsive to selections. Is there an analogous function for inside the tool?

Re: Need an inverse to the Pick function

marcus_sommer — Mon, 19 Jan 2015 15:51:43 GMT

If you could find some logic to defined your expressions to certain results - most often through ceil() or floor() - it is possible to use a pick(match()) instead a nested if-loop.

http://community.qlik.com/message/593232#593232

Also a class() could be possible then the buckets could be per if-loop general various if the buckets-ranges don't overlap eachother.

Further you could simplify your expressions with parametrized variables like:

vE_Decile_max = fractile($(vE_Decile_max_Calc),$1)

and then

if($(vE_PP_Total_Sales) <= $(vE_Decile_max(0.1)), 1, ...

- Marcus

Re: Need an inverse to the Pick function

Mon, 19 Jan 2015 16:28:38 GMT

Yeah I had thought about trying to use the ceiling function to force a matchable value, but since the values aren't following any kind of equation, it would be tough to find a transformation that would work universally. It may be possible, but would take too long.

Would you be able to expand on this statement:

Also a class() could be possible then the buckets could be per if-loop general various if the buckets-ranges don't overlap eachother.

I'm not really sure what you were trying to say.

The parameter idea is a good one though. I didn't realize that was possible. I'll try implementing that now.

Re: Need an inverse to the Pick function

marcus_sommer — Mon, 19 Jan 2015 17:04:08 GMT

If you could define your expression-result to a integer value like 0,1,2,3 ... your max. value - then you could per excel or a loop generate a list or table which contains these values and their matching.

Example for Buckets:

if(value >= 0 and value <= 100, 10,

if(value > 100 and value <= 200, 20,

...

Not overlapping meant: the first if couldn't go until 90 as value and the second couldn't have 30 as bucket-size then it wouldn't match properly. In most cases the calculations are correct but the dimension showed wrong clusters.

- Marcus

Re: Need an inverse to the Pick function

Mon, 19 Jan 2015 17:20:22 GMT

Would this work with dynamic buckets? Because the limits on each bucket change with the selections. The class function requires uniform buckets right? If I could define a class function with specifiable range values then that would be perfect, but I don't think this is possible.

Overlapping buckets isn't a problem, and I have the range from 0 to the max possible value completely covered which makes that aspect a little easier.

Thanks for the help in all this though.

Re: Need an inverse to the Pick function

MarcoWedel — Mon, 19 Jan 2015 22:35:57 GMT

please provide sample data and expected output.

thanks

regards

Marco

Re: Need an inverse to the Pick function

petter — Mon, 19 Jan 2015 23:31:32 GMT

Actually I borrowed a "Decile Analysis" from QlikFix.com and modified the expression from a nested IF into a pure calculation like this:

As the different deciles are added you get a result of 1,2,5,9,14,20,27,35,44,54 which corresponds to 1..10 - hence the Match() to convert into the right decile ...

According to different hints and tips found on Qlik Community and elsewhere like books ... the calculation should be significantly quicker than using ifs - I leave the testing for you though ...

Good luck !

Re: Need an inverse to the Pick function

petter — Tue, 20 Jan 2015 00:05:34 GMT

Acually I couldn't resist giving it a test - Ifs versus pure calculation on 1 million customers:

Ifs took on average 1780 milliseconds to recalculate. Calculation too on average around 2900 milliseconds if using the Match() and without the Match() it took 1580 milliseconds ...

Re: Need an inverse to the Pick function

Tue, 20 Jan 2015 14:48:44 GMT

Hi Marco,

Petter's solution actually did the trick. Thanks for looking at this though!

Spencer

Re: Need an inverse to the Pick function

Tue, 20 Jan 2015 14:49:42 GMT

Yes this worked perfectly!

I hadn't considered reducing the the individual comparisons to boolean and summing up individual possibilities. Thanks for the help!