topic Re: Pick match or Set Analysis in QlikView

Pick match or Set Analysis

helen_pip — Wed, 09 Aug 2017 14:06:15 GMT

Dear Qlikview user

I have a button called Type and when the user clicks on it, ,,y expression is designed to show the relevant value. However my expression is using an If statement and I would like to try and use set analysis instead, so it only evaluates the required data

My Button is called "Type"

My expression is as follows:

If(TYPE = 'INCOMPLETE',

max({<Month,QuarterName,FiscalYear,FactDate={'$(=$(e_RTT_Max_Date))'}>}DAYS_WAIT)/7

If(TYPE = 'ADM' OR TYPE = 'NON ADM',

max({<Month,QuarterName,FiscalYear,MonthYear={'$(=$(e_RTT_Max_Month))'}>}DAYS_WAIT)/7

))

I know Pick(Match is better than and If, but I am not sure how I would better that as I want to use set analysis instead

Any help would be greatly appreciated

Thanks

Helen

Re: Pick match or Set Analysis

Anil_Babu_Samineni — Wed, 09 Aug 2017 14:08:58 GMT

Does If...else condition getting any performance issues for you? I don't think this equation kills the performance.

Re: Pick match or Set Analysis

helen_pip — Wed, 09 Aug 2017 14:10:46 GMT

Hi,

Yes we are trying to get way from all If statements if necessary and try and use set analysis as much as possible, but am unsure how to fit this expression into Set analysis

Kind Regards

Helen

Re: Pick match or Set Analysis

effinty2112 — Wed, 09 Aug 2017 14:19:11 GMT

Hi Helen,

If Type can only be 'ADM', 'NON ADM' or 'INCOMPLETE' then we can simplify your expression to:

If(TYPE = 'INCOMPLETE',

max({<Month,QuarterName,FiscalYear,FactDate={'$(=$(e_RTT_Max_Date))'}>}DAYS_WAIT)/7,

max({<Month,QuarterName,FiscalYear,MonthYear={'$(=$(e_RTT_Max_Month))'}>}DAYS_WAIT)/7)

I much prefer Pick(Match(...)) to nested if statements but if you can simplify to the above then the if statement is fine. I don't think any more set analysis would improve things.

Cheers

Andrew

Re: Pick match or Set Analysis

helen_pip — Wed, 09 Aug 2017 14:49:43 GMT

Hello Andrew

Thank you for your suggestion...It works and nice to know that it is OK to use an If statement

However, what if set analysis was insisted upon, how could I adapt the If statement to become set analysis

Kind Regards

Helen

Re: Pick match or Set Analysis

effinty2112 — Wed, 09 Aug 2017 15:42:58 GMT

Hi Helen,

I don't think further set analysis is appropriate. Let's take a step back and think about the purpose of set analysis. Set analysis allows us to calculate expressions based on the records we choose independent of those associated with the current selected dimension values.

We want our choice of expression (either of the two expressions in the if statement) to be based on the user's selection of TYPE so there's no point is writing a piece of set analysis to evade the user's choice of TYPE.

If we are looking at a subset of the records associated with the current selections we can do this in different ways. We can use such expressions as Sum(if(Director <>'Ingmar Bergman', [Box Office Takings]).

In this case rewriting as Sum({$<Director -= {'Ingmar Bergman}>}[Box Office Takings]) is preferred for performance and awareness of this is probably why the edict you've been asked to work to was proposed.

An if statement inside an expression can be improved by re-writing it using set analysis but in your example it's the other way round - we have two expressions inside an if statement so it's a different situation.

Kind regards

Andrew

Re: Pick match or Set Analysis

helen_pip — Wed, 09 Aug 2017 19:40:41 GMT

Hello Andrew

Thank you for spending the time to answer my question. It makes sense now and really appreciate your explanation

Kind Regards

Helen