topic Re: Simplifying an if expression with null conitions into set analysis in QlikView

Simplifying an if expression with null conitions into set analysis

Tue, 18 Feb 2014 20:43:03 GMT

Hello

I am trying to convert an if expression into set analysis but it has multiple not null conditions and I am little confused on how to write this expression in set.

sum(aggr(If([Currency]='Transactional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)) ,[Price List],

([Currency]='Functional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

[Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',

[EX Rate]),

([Currency]='Reporting' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

([Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',[EX Rate]),0)) *

max(if([Revenue Schedule Type]='RPRO-COGS',[Reporting Currency Ex Rate]),0)

)

,[Transaction ID]))

Re: Simplifying an if expression with null conitions into set analysis

stevedark — Tue, 18 Feb 2014 20:49:42 GMT

Hi Rizwan,

Are the ATTRIBUTE fields in the same table as the other fields in the expression? Or are the values NULL because there is an association that is not happening?

If it is the former (or to an extent the latter) it is always helpful to remove NULL values in the load and put in a known string - you can use this in Set Analysis.

In the load script:

if(isnull(ATTRIBUTE49), 'missing', ATTRIBUTE49) as ATTRIBUTE49,

You can then match on 'missing' in your expression.

It is always better to try and sanitize your data on the way in than deal with complexities in front end expressions.

- Steve

Re: Simplifying an if expression with null conitions into set analysis

Tue, 18 Feb 2014 20:55:06 GMT

Hello Steve Dark

Maybe I was not able to make my question clear.

I understand your point but in this situation I cannot do it the way you are saying because I am using these fields in some other expressions too so I cannot hard code them.

What I want to know is

How would I be able to convert the above expression into set analysis format?

I hope this explains my question

Thanks

Re: Simplifying an if expression with null conitions into set analysis

Tue, 18 Feb 2014 20:56:56 GMT

You can use a combination of set analysis "not equal" syntax with OR conditions between. Something like:

sum({<Set Analysis Condition1> + <Set Analysis Condition2> + <Set Analysis Condition3>...} Field1)

where Set Analysis Condition(x) =

Here, the "+" between each set expression act as "OR" statements, and "-=" is "Not Equal to" in set analysis.

Re: Simplifying an if expression with null conitions into set analysis

stevedark — Tue, 18 Feb 2014 21:01:10 GMT

Set Analysis uses the same algorithms as a standard selection in QlikView and you can not select a NULL. You can do things like -={'*'} to try and exclude everything that isn't a NULL, but I have found in the past that any time you try and work around NULLs you can get spurious results.

If you need the NULL values on those fields you could create two copies of the fields, eg:

ATTRIBUTE49,

if(isnull(ATTRIBUTE49), 'missing', ATTRIBUTE49) as ATTRIBUTE49_NoNull,

Steve

Re: Simplifying an if expression with null conitions into set analysis

Tue, 18 Feb 2014 21:38:21 GMT

Hello @Paul

I am still having issues converting it. Can you help me simplify this expression or convert it into the most efficient form from a frontend perspective. I am a little confused.

([Currency]='Functional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

[Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',

[EX Rate]),

([Currency]='Reporting' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

([Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',[EX Rate]),0)) *

max(if([Revenue Schedule Type]='RPRO-COGS',[Reporting Currency Ex Rate]),0)

)

,[Transaction ID]))

Thanks