topic Re: Set analysis to avoid if's? in QlikView

Set analysis to avoid if's?

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

I have and expression like this:

IF(GetSelectedCount(SequenceInd)=0,

Count({$<RecOpenStatus = {'Open'}, SequenceInd = {'Seq'}>}DISTINCT ReceivableID),

Count({$<RecOpenStatus = {'Open'}>}DISTINCT ReceivableID))

I don't think what I want to do is possible but I am hoping I am wrong and that I can increase performance. Is there another way to have my expression as Count({$<RecOpenStatus = {'Open'}, SequenceInd = {'Seq'}>}DISTINCT ReceivableID) but if another SequenceInd is selected, the result will reflect that? I am looking to reduce the IF statements in my application.

Re: Set analysis to avoid if's?

neelamsaroha157 — Mon, 27 Aug 2018 18:14:13 GMT

Can you try this -

Count({$<SequenceInd = {$(=GetSelectedCount(SequenceInd)=0)},RecOpenStatus = {'Open'}, SequenceInd = {'Seq'}>}DISTINCT ReceivableID),Count({$<RecOpenStatus = {'Open'}>}DISTINCT ReceivableID))

Re: Set analysis to avoid if's?

greend21 — Mon, 27 Aug 2018 18:29:12 GMT

This returned null, althought I had to only use the part before the comma since using the entire thing is an invalid dimension.

For the first half, did you mean to put GetCurrentSelection instead of GetSelectedCount? I can almost see how that will work but I need it to default to 'Seq' still if possible.

Re: Set analysis to avoid if's?

Anonymous — Mon, 27 Aug 2018 18:30:40 GMT

You could use a variable to control the possible value of SequenceInd in your set analysis.

You would want to first create a variable; something like this:

vSeq_Selected = IF(GetFieldSelections(SequenceInd) <> 'Seq', GetFieldSelections(SequenceInd), 'Seq)

Then in your set analysis you would replace

SequenceInd = {'Seq'} With SequenceInd = {'$(vSeq_Selected)'}

Now If SequenceInd is not selected it will default to calculate it being Seq; if it is selected, it should calculate for the selected value.

Re: Set analysis to avoid if's?

trdandamudi — Mon, 27 Aug 2018 18:31:51 GMT

Neelam, I think you are missing double quotes:

Count({$<SequenceInd = {$("=GetSelectedCount(SequenceInd)=0")},RecOpenStatus = {'Open'}, SequenceInd = {'Seq'}>}DISTINCT ReceivableID),Count({$<RecOpenStatus = {'Open'}>}DISTINCT ReceivableID))

Re: Set analysis to avoid if's?

neelamsaroha157 — Mon, 27 Aug 2018 18:35:12 GMT

Thanks Thirumala for pointing that out. Appreciate it. Sometimes these little things gets missed out when you are directly writing expression without the actual qv file .

Re: Set analysis to avoid if's?

greend21 — Mon, 27 Aug 2018 18:37:32 GMT

I was thinking maybe something like this, but do you know if this would improve performance at all since there is still an IF statement? I had read that IF statements can hinder performance, which seems to be the case for me so I was looking for ways to eliminate them if possible.

Re: Set analysis to avoid if's?

greend21 — Mon, 27 Aug 2018 18:45:40 GMT

This still doesn't work. I don't think qlik will take two separate measures just separated by a comma or at least it doesnt work for me.

Re: Set analysis to avoid if's?

Anonymous — Mon, 27 Aug 2018 19:00:03 GMT

Unfortunately, I couldn't tell you without testing. If the IF Statement you reference is an Object Expression, then I could see this solution potentially saving time because of when it's calculated. If that IF Statement is a variable just the same as the solution I propose; it may not be.

Re: Set analysis to avoid if's?

greend21 — Mon, 27 Aug 2018 19:45:32 GMT

I was really hoping this would work so I was testing performance next to another object that had the IF statement, and if I changed the selection the object with the IF statement calculated about 2 seconds quicker than the one using the variable within the set analysis.

Re: Set analysis to avoid if's?

vishsaggi — Mon, 27 Aug 2018 20:05:53 GMT

Have not tested but may be you can try below and see

= Pick(GetSelectedCount(SequenceInd),

Count({$<RecOpenStatus = {'Open'}, SequenceInd = {'Seq'}>}DISTINCT ReceivableID),

Count({$<RecOpenStatus = {'Open'}>}DISTINCT ReceivableID))

Re: Set analysis to avoid if's?

marcus_sommer — Tue, 28 Aug 2018 08:01:49 GMT

Testing the UI (expressions) performance isn't very easy and without a systematically approach by evaluating the mem-files and/or looking within the document properties it's more a guessing than a monitoring. Nevertheless if there are not many objects so that the opening/calculating times might be added to noticable waiting-times the simpler approach of just using two objects/expressions and enabling either this one or the other will work to notice a significantly delaying or not.

A premise for it is to exclude any caching of the objects/calculations. This means you need to close QlikView after each test and by the test you shouldn't do anything else within the app as directly opening the sheet / activating the object / selecting one or maye a few values. Otherwise the caching could impact the results.

In your case I doubt that the if-approach is faster than the variable-suggestion from meggesto because the condition is calculated ones and applied to all rows meanwhile the condition in the if-approach will be calculated multiple times (for each row) and also the branches of the if are calculated for each row.

Therefore I suggest to use such a variable-approach whereby you don't need mandatory to create a "normal" variable else using an adhoc-variable will often work, too. This means something like:

Count({$<RecOpenStatus = {'Open'},

SequenceInd *= {"$(=IF(GetSelectedCount(SequenceInd)=0, 'Seq', '*'))"}>} DISTINCT ReceivableID)

- Marcus