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

[Anonymous]

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

[Oleg_Troyansky]

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!

[MK_QSL]

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.....