topic Re: Count(Distinct) in an IF statement in the Load script in QlikView

Count(Distinct) in an IF statement in the Load script

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

I am trying to use a Count(Distinct) inside a nested IF statement in the script Load statement,but it does not seem to work. It does work fine as an expression though.

LOAD

IF((PROVIDER='W' AND [PROC CODE] like '7*')OR(PROVIDER='R' AND [PROC CODE] like '7*'),0,

IF([REG FSC]=61,0,

IF((MODIFIER like 'A*') OR (MODIFIER like 'B*') OR (MODIFIER like 'C'),0,

IF((LOC_GROUP='DYL')AND([POST DATE]-[SERV DATE])<='45',0,

IF((LOC_GROUP='HOS')AND([POST DATE]-[SERV DATE])<=3,0,COUNT(DISTINCT(CASE))))))) AS OUTLIER

Re: Count(Distinct) in an IF statement in the Load script

Oleg_Troyansky — Thu, 29 Dec 2016 22:20:00 GMT

In order to use any aggregation functions in the load script, you should add a GROUP BY clause to your load. In order to make it work, you should include all the fields that are used in your IF conditions, as the GROUP BY dimensions.

Alternatively, you can simply assign OUTLIER = CASE when the condition is true and not aggregate in script. Then, you could aggregated in chart using Count(Distinct OUTLIER)

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

Re: Count(Distinct) in an IF statement in the Load script

MK_QSL — Thu, 29 Dec 2016 22:29:45 GMT

Load

IF(Match(PROVIDER,'W','R') and WildMatch([PROC CODE],'7*'),0

,IF([REG FSC] = '61', 0

,IF(WildMatch(MODIFIER,'A*','B*','C*'),0

,IF(LOC_GROUP = 'DYL' and [POST DATE] - [SERV DATE] <= 45, 0

,IF(LOC_GROUP = 'HOS' and [POST DATE] - [SERV DATE] <= 3, 0

,COUNT(DISTINCT CASE)

))))) as OUTLIER

Group by SOMETHING.....