Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Error on Join in QlikView Script

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Error on Join in QlikView Script

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;

2 Replies
MVP
MVP

Re: Error on Join in QlikView Script

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;

Not applicable

Re: Error on Join in QlikView Script

Thanks for your help! That worked perfectly.

Community Browser