Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
All fields have to be either used with an aggregation function or part of the group by clause:
hope this helps
regards
Marco
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