2 Replies Latest reply: Jun 2, 2016 3:30 AM by Manuel Capella RSS

    Error in resident Load

    Shashank Sawant

      I have a below Script which fails in Qlik Sense stating error messages as  Invalid Expression please could anyone help?

       

      Script is as below:

      [Data]

      LOAD
          Sellg_div_cd,
          Region,
          District,
          EMP,
          Layer,
          Prod_NM,
          Current,
          Baseline,
          PRO_Baseline,
          Wk_End
      FROM [lib://Data/Data.xlsx]
      (ooxml, embedded labels, table is [Non-Retail]);
      Concatenate

      LOAD
           Region,
          District,
          EMP,
          Layer,
          Prod_NM,
          Current,
          Baseline,
          PRO_Baseline,
          Wk_End
      FROM [lib://Data/Data.xlsx]
      (ooxml, embedded labels, table is [Retail]);

       

      [EMP]:

      Load EMP,

      If(Prod_NM ='ABC' OR Prod_NM='XYZ',Sum(PRO_Baseline))AS DRV_BASELINE,

      If(Prod_NM='ABC' or Prod_NM='XYZ',Sum(Current))AS DRV_Current,

      If(Prod_NM='PQR',Sum(PRO_Baseline))AS MV_BASELINE,

      If(Prod_NM='PQR',Sum(Current))AS MV_Current

      Resident Data

      Order by EMP;

       

      Error I receive while running this code is Invalid Expression.

      I am have one excel with Multiple sheets, I need to load data from all sheets into one single table.

      I have to take Product ABC and Product XYZ and Get there Sum for baseline values and Current values.

       

      Once I Receive these values there are many more calculations to be done on these values hence need them to be calculated in Script instead Of Expression.

       

      Please help.

        • Re: Error in resident Load
          Edgar Vazquez

          Try this

           

          [EMP]:

          Load EMP,

          If(Prod_NM ='ABC' OR Prod_NM='XYZ',Sum(PRO_Baseline))AS DRV_BASELINE,

          If(Prod_NM='ABC' or Prod_NM='XYZ',Sum(Current))AS DRV_Current,

          If(Prod_NM='PQR',Sum(PRO_Baseline))AS MV_BASELINE,

          If(Prod_NM='PQR',Sum(Current))AS MV_Current

          Resident Data

          Group by Prod_NM

          Order by EMP;

          • Re: Error in resident Load
            Manuel Capella

            [EMP]:

            Load EMP,

            If(Prod_NM ='ABC' OR Prod_NM='XYZ',Sum(PRO_Baseline))AS DRV_BASELINE,

            If(Prod_NM='ABC' or Prod_NM='XYZ',Sum(Current))AS DRV_Current,

            If(Prod_NM='PQR',Sum(PRO_Baseline))AS MV_BASELINE,

            If(Prod_NM='PQR',Sum(Current))AS MV_Current

            Resident Data

            GROUP BY EMP, Prod_NM

            Order by EMP;

             

            OR

             

            [EMP]:

            Load EMP,

            Sum(If(Prod_NM ='ABC' OR Prod_NM='XYZ',PRO_Baseline))AS DRV_BASELINE,

            Sum(If(Prod_NM='ABC' or Prod_NM='XYZ',Current))AS DRV_Current,

            Sum(If(Prod_NM='PQR',PRO_Baseline))AS MV_BASELINE,

            Sum(If(Prod_NM='PQR',Current))AS MV_Current

            Resident Data

            GROUP BY EMP

            Order by EMP;