topic Re: Error on Join in QlikView Script in QlikView

Error on Join in QlikView Script

Wed, 07 Aug 2013 21:45:47 GMT

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!

Re: Error on Join in QlikView Script

swuehl — Wed, 07 Aug 2013 22:16:44 GMT

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;

Re: Error on Join in QlikView Script

Thu, 08 Aug 2013 00:31:50 GMT

Thanks for your help! That worked perfectly.