5 Replies Latest reply: Oct 18, 2018 3:08 AM by patrice salem RSS

    Resident table, group, calculate and add new fields

    patrice salem

      Hello

       

      I'm loading a file called ZSQ042 and, using this great forum and community (sincerely and truly amazed by the level of support), I have managed to create a first simple resident table to group some fields

       

      [ZSQ042]

      LOAD

      country,

      salesman,

      product_group,

      order_number,

      date_order,

      qty

      from

      ZSW042.xls;

       

      [Aggregate_ZSQ042]

      NoConcatenate

      LOAD

      country,

      salesman,

      product_group,

      year(date_order),

      sum(qty) as Total_Qty

      group by country, salesman, product_group, year

       

      Resident ZSQ042;

      drop Table ZSQ042;

       

       

      I would like to add a few columns to my Aggregate_ZSQ042 table that will be used later in Qlik view along with sales targets (defined by country, salesman and product_group) :

      [total qty for Year N-2]

      [total qty for Year N-1]

      [total qty for Year N until enf of previous week]

      [total qty for Year N-1 until end of previous week]


      Year N being year(now())


      I imagine that I have to use aggregate functions, where clauses...but I have no clue how to do it.


      Do you have any idea how to proceed ?


      Thanks in advance for your support

      Patrice