topic Re: Set analysis and if conditions in QlikView

Set analysis and if conditions

Anonymous — Thu, 22 May 2014 17:59:40 GMT

I've read that using set analysis can perform much faster than conditional if statements. I have a scatterplot chart that uses one dimension with 2 expressions - one expression is a field - the other expression is conditional based on what is selected in a listbox.

I'm attaching an example where I am using a conditional statement for one of the expressions in the scatterplot based on the selection in the listbox. How would I use set analysis for this example?

I am using this conditional expression in a variety of places on my application and want try to make the rendering of these charts and objects faster.

Re: Set analysis and if conditions

hic — Fri, 23 May 2014 09:21:08 GMT

Set analysis will not help you here. Set analysis is in principle like a selection that is made for that expression only, and is evaluated before the expression is calculated.

In your case, you have a data model with two unconnected tables that looks like

and then you use an if function in the scatter chart to create a link between S_ID and the right number in the left table. This type of logic is exactly what QlikView is good at. So I would instead build this logic into the data model:

Crosstable (S_ID, number,2)

LOAD id,

number_1,

number_2,

number_3,

number_4,

number_5

FROM scatterplot_issue.txt;

LOAD Name,

'number_' & S_ID as S_ID

FROM Selection.txt ;

and then you need to change your scatter chart to use the following two expressions instead:

Expr1: Avg(number)

Expr2: Avg(number_1)

HIC

Re: Set analysis and if conditions

Fri, 23 May 2014 13:27:28 GMT

Carry,

i can see, that your selection defines rather expression (column) than data set to process.

Of course you may do as Henric suggested.

Anyway, especially if you have large volumes it may be better to still have 5 columns.

You may consider to use:

pick(S_ID, number_1,number_2,number_3,....) instead your nested if.

regards

Darek

Re: Set analysis and if conditions

Anonymous — Fri, 23 May 2014 14:07:29 GMT

I did try the suggestion you made, Henric - but saw no difference in performance on my application.

Darek, is the pick() function better for performance than loading all of the data as Henric suggested?

my main table has a lot of other attributes for the id and I would probably not want to use crosstable for the whole thing as it would generate a lot of extra data.

Thanks,

Cary