Error on Join in QlikView Script

[#1]

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!

[swuehl · Accepted solution]

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;

[#3]

Thanks for your help! That worked perfectly.