if condition with group by return errors

[vengadeshpalani]

Hi

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;

[MarcoWedel]

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

Load

hope this helps

regards

Marco

[rahulpawarb · Accepted solution]

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