Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am getting an error on the Inner Join below. It is erroring out on the line with the If statement and it states that EVSFY2DomMetric cannot be found. Any idea how to make this work?
EVS_FY1_Temp:Load FACNUM,
OP_TEMP_EVS.LOCATION,
OP_TEMP_EVS.FY1DOMMETRIC AS EVSFY1DomMetric
RESIDENT OP_TEMP_EVS
WHERE MONTHNAME = 'FY1';
EVS_FY2_Temp:Load FACNUM,
OP_TEMP_EVS.LOCATION,
OP_TEMP_EVS.FY2DOMMETRIC As EVSFY2DomMetric
RESIDENT OP_TEMP_EVS
WHERE MONTHNAME = 'FY2';
Inner JOIN (EVS_FY2_Temp)LOAD FACNUM,
OP_TEMP_EVS.LOCATION,
If(EVS_FY2_Temp.EVSFY2DomMetric <> null(), EVS_FY2_Temp.EVSFY2DomMetric, EVS_FY1_Temp.EVSFY1DomMetric)AS EVSDominantMetric
RESIDENT EVS_FY1_Temp;
DROP TABLE EVS_FY1_Temp;
Thanks!
Field EVSFY2DomMetric is not part of your input table EVS_FY1_Temp, so you can't reference it in your load.
Maybe this works:
EVS_FY1_Temp:
Load FACNUM,
OP_TEMP_EVS.LOCATION,
OP_TEMP_EVS.FY1DOMMETRIC AS EVSFY1DomMetric
RESIDENT OP_TEMP_EVS
WHERE MONTHNAME = 'FY1';
JOIN (EVS_FY1_Temp) Load FACNUM,
OP_TEMP_EVS.LOCATION,
OP_TEMP_EVS.FY2DOMMETRIC As EVSFY2DomMetric
RESIDENT OP_TEMP_EVS
WHERE MONTHNAME = 'FY2';
RESULT:
LOAD FACNUM,
OP_TEMP_EVS.LOCATION,
If(not isnull(EVSFY2DomMetric),EVSFY2DomMetric, EVSFY1DomMetric) AS EVSDominantMetric
RESIDENT EVS_FY1_Temp;
DROP TABLE EVS_FY1_Temp;
Field EVSFY2DomMetric is not part of your input table EVS_FY1_Temp, so you can't reference it in your load.
Maybe this works:
EVS_FY1_Temp:
Load FACNUM,
OP_TEMP_EVS.LOCATION,
OP_TEMP_EVS.FY1DOMMETRIC AS EVSFY1DomMetric
RESIDENT OP_TEMP_EVS
WHERE MONTHNAME = 'FY1';
JOIN (EVS_FY1_Temp) Load FACNUM,
OP_TEMP_EVS.LOCATION,
OP_TEMP_EVS.FY2DOMMETRIC As EVSFY2DomMetric
RESIDENT OP_TEMP_EVS
WHERE MONTHNAME = 'FY2';
RESULT:
LOAD FACNUM,
OP_TEMP_EVS.LOCATION,
If(not isnull(EVSFY2DomMetric),EVSFY2DomMetric, EVSFY1DomMetric) AS EVSDominantMetric
RESIDENT EVS_FY1_Temp;
DROP TABLE EVS_FY1_Temp;
Thanks for your help! That worked perfectly.