5 Replies Latest reply: Aug 22, 2015 4:09 PM by Laura Sak. RSS

    Ho to creat a new table from two different table?

    Laura Sak.

      Hello, everybody.

       

      I'm new in Qlik Sense and I need some help.

       

       

       

      I have two tables:

      Item

      Cathegory

      Category

      Item.no

      Category

      Description

      1Milk"Milk A"
      2Bologna"Pork"
      3Pasta"Fusilli"
      4Milk"Milk B"
      5Bologna"Beef"

       

      and

      Ledger

       

      Item.noSalesDate
      11002011.01.01
      52002011.02.02
      23002012.03.03
      44002012.03.03
      15002013.01.01

       

       

      I need create a new variable in script to Qlik sense script editor from two different tables :

      [a]: Category, sum(sales) by Category

      It have to look:

      Header 1Header 2
      Milk

      1000 // 100+400+500

      Bologna500 //  200+300 

       

      If I'm using

      [KA1]:

      LOAD

      Category as K

      FROM [lib://ITEM/item.qvd]

      (qvd);

       

      join LOAD

          Sales as AP

      FROM [lib://ITEM LEDGER/item_ledger.qvd]

      (qvd);

       

      I've got a cross table and duplicated values:

       

      KAP
      Milk100
      Milk200
      Milk300
      Milk400
      Milk500
      Bologna100
      Bologna200
      .......

       

      I tried mapping too:

       

      [KATE]:

      LOAD

          item.no as IK,

          Category as IC

      FROM [lib://ITEM/item.qvd]

      (qvd);

       

      [keysales]:

      LOAD

          item.no as Ikey,

          sales as Isales

      FROM [lib://ITEM LEDGER/item_ledger.qvd]

      (qvd);

       

       

      [map2]:

      mapping    

      LOAD

          Ikey,

          Isales

      resident [keysales];

       

       

      [K2]:

      load *, ApplyMap ('map2', IK )

          as sumA

         Resident  [KATE]

       

      It's not working too. I've missing a data. For item.no 1, sumA is only 100.

       

      How to create a new variable with cathegory, and sum sales?

       

      Thanks for help in advance.