2 Replies Latest reply: Aug 7, 2013 8:31 PM by Shannon Spangler RSS

    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!

        • Re: Error on Join in QlikView Script
          Stefan Wühl

          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;