topic Re: IF statement to Set Analysis in QlikView

IF statement to Set Analysis

ellenblackwell — Tue, 10 Mar 2015 07:30:25 GMT

Trying to convert the following IF statement into set analysis. Green, bolded components of formula are the ones I am struggling with getting into working set analysis formula. Any help would be greatly appreciated. Thanks!

Ellen

=count (distinct if(FLG_ABC_Captured='Y' and FLG_XYZ_Captured_CMPTV='Y' and [ABC GEO ID]=[XYZ GEO ID] and [Price1]>[Price2], [POC ID]))

Re: IF statement to Set Analysis

Anonymous — Tue, 10 Mar 2015 07:50:37 GMT

Hi Ellen,

Why you want to use set analysis,

As you are doing row by row comparison if statement is correct approach to do it. [Price1]>[Price2] ...

Re: IF statement to Set Analysis

jonathandienst — Tue, 10 Mar 2015 07:53:05 GMT

=count ({<FLG_ABC_Captured={'Y'}, FLG_XYZ_Captured_CMPTV={'Y'}>} distinct if([ABC GEO ID] = [XYZ GEO ID] and Price1 > Price2, [POC ID]))

You can't do the other two in a set expression - this is a row by row comparison of two fields, but the set expression is calculated once for the chart/table, not once per row.

The only way to avoid the Count(If()) here is to create a flag in the load script - something like

LOAD

...

If([ABC GEO ID] = [XYZ GEO ID] and Price1 > Price2, 1, 0) As Flag1,

...

And now add a Flag1 = {1} clause to the set expression.

HTH

Jonathan

Re: IF statement to Set Analysis

ellenblackwell — Tue, 10 Mar 2015 09:07:38 GMT

Thank you for the accurate, more elegant approach. I have many formulas that look like the one in my original request. The table containing the formulas loads excruciatingly slowly.

I also want to create a total in a Text Box object. Without the additional flag you mentioned, I will have to use the same formula you updated, correct?

Thank you!

Re: IF statement to Set Analysis

jonathandienst — Tue, 10 Mar 2015 09:26:38 GMT

In the text box, the Flag option will work without change, but the Count(If()) will need to be in an aggr function for the row by row comparisons. Something like

Sum(Aggr(Count({<FLG_ABC_Captured={'Y'}, FLG_XYZ_Captured_CMPTV={'Y'}>} distinct if([ABC GEO ID] = [XYZ GEO ID] and Price1 > Price2, [POC ID])), dim1, dim2)) //check brackets...

where dim1, dim2 is a comma separated list of the same dimension(s) in your table (or at least return unique values for the row by row compared fields)

And the expression will take as long to evaluate as the table...