Need an inverse to the Pick function

[#1]

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

[#2]

Try to use Switch Statment...

Vinay

[#3]

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?

[marcus_sommer]

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

[#5]

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.

[marcus_sommer]

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

[#7]

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.

[MarcoWedel]

please provide sample data and expected output.

thanks

regards

Marco

[petter · Accepted solution]

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 !

[petter]

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