Hi,
I have a group by query, i am getting Error 1 (below) since i am using the alias in group by for the if formula,,, what should i use in order not to get error,,,
even if i remove COS_GI_Status from my query i still get Error2,,,
my group by works if i have only one field in addition to the count field,,, if i select more fields i get error
Query:
load CS_GEAR_ID,
CS_Current_State as COS_Status,
CS_Date as COS_Date,
COUNT(CS_GEAR_ID) as COS_Count,
if(match(CS_Current_State,'New', 'Planned', 'Strategic Investment')>0, 'Total Live Apps','Total Decom/Withdrawn Apps') AS COS_GI_Status
Resident Current_Status
Group by COS_Current_State, COS_Date, COS_GI_Status;
Error 1:
Field not found - <COS_GI_Status>
Count_of_Status:
load CS_GEAR_ID,
CS_Current_State as COS_Status,
CS_Date as COS_Date,
COUNT(CS_GEAR_ID) as COS_Count,
if(match(CS_Current_State,'New', 'Planned', 'Strategic Investment')>0, 'Total Live Apps','Total Decom/Withdrawn Apps') AS COS_GI_Status
Resident Current_Status
Group by CS_Current_State, CS_Date, COS_GI_Status
Error 2:
Invalid expression
Count_of_Status:
load CS_GEAR_ID,
CS_Current_State as COS_Status,
CS_Date as COS_Date,
COUNT(CS_GEAR_ID) as COS_Count
Resident Current_Status
Group by CS_Current_State, CS_Date
