topic Re: if condition with group by return errors in QlikView

if condition with group by return errors

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

Why it return invalid expression

load JobID,

count(DISTINCT Date) as DateCount

if(count(DISTINCT Date) < GreenLimit,'GF',

if(count(DISTINCT Date) <= YellowMin and count(DISTINCT Date) >= YellowMax , 'YF',

if(count(DISTINCT Date) >= GreenLimit, 'RF','Null'))) as KPI_Flag

Resident Fact Group by JobID;

Re: if condition with group by return errors

MarcoWedel — Fri, 21 Apr 2017 17:43:38 GMT

All fields have to be either used with an aggregation function or part of the group by clause:

Load

hope this helps

regards

Marco

Re: if condition with group by return errors

rahulpawarb — Fri, 21 Apr 2017 17:59:13 GMT

Hello Vengadesh,

Trust that you are doing good!

Please refer below sample scripts:

Table:
load JobID,
     COUNT(DISTINCT Date) AS DateCount,
     If(COUNT(DISTINCT Date) < GreenLimit,
     'GF',
  If(COUNT(DISTINCT Date) <= YellowMin AND COUNT(DISTINCT Date) >= YellowMax ,
    'YF',
    If(COUNT(DISTINCT Date) >= GreenLimit,
       'RF',
   'Null'))) AS KPI_Flag
Resident Fact  
Group By 
      JobID,
      GreenLimit,
   YellowMin,
   YellowMax;
   
// OR 

 Table:
 LOAD JobID,
      DateCount,
   If(DateCount < GreenLimit,
      'GF',
  If(DateCount <= YellowMin AND DateCount >= YellowMax ,
     'YF',
            If(DateCount >= GreenLimit,
    'RF',
    'Null'))) AS KPI_Flag;
 LOAD JobID,
      GreenLimit,
   YellowMin,
   YellowMax,
   COUNT(DISTINCT Date) AS DateCount
 Resident Fact
 Group By 
      JobID,
      GreenLimit,
   YellowMin,
   YellowMax;

P.S.: You have also missed to add a comma ( i.e. ,) after DateCount field.

Regards!

Rahul