16 Replies Latest reply: Sep 12, 2017 1:13 AM by Agrim Sharma RSS

    link different table with same fields

    Agrim Sharma

      I have two excel files, In which one is sales table and another one is order table.

      There are two fields common in both the excel sheets, “party_name” and “date

      Eg,

      party_name       sales_amount               date

      X                                         2                          2/2/2017

      Y                                         3                           6/2/2017

      Z                                         8                           13/1/2017

      A                                        1                           5/7/2017

       

      When I join them the output is not coming as desired output and when I do not join them I get synthetic key but get right results

      Eg,

      party_name       booking_amount                            date

      X                                         2                                        2/6/2017

      A                                        1                                         5/3/2017

       

      I want a output in pivot table as quarter wise sales and booking amount, for which I have created an calendar by script.

        • Re: link different table with same fields
          Anil Babu Samineni

          May be create link table like below

           

          Sales:

          LOAD party_name, sales_amount, date, party_name & '|' & date as Key

          FROM

          Sales_Table;

           

          Order:

          LOAD party_name, booking_amount, date, party_name & '|' & date as Key

          FROM

          Order_Table;

           

          //Creating Link Table for 2 fact tables which is Sales and Order

           

          Link_Table:

          Load DISTINCT Key, sales_amount resident Sales;

          Concatenate(Link_Table)

          Load DISTINCT Key, booking_amount resident Order;


          // Here, I am removing the fields because we already generate the Key for Matching numbers to each


          Drop Fields part_name, date from Sales;

          Drop Fields part_name, date from Order;

            • Re: link different table with same fields
              Agrim Sharma

              Hi,

              Thanks for reply,

              i have,

               

               

              Master:

              LOAD

                  factory,

                  "date",

                  party_no,

                  party_name,

                  cics,

                  category,

                  market,

                  im_code,

                  im_descr,

                  qty,

                  "rate",

                  basicvalue

                 

               

              FROM [lib://Sales/SALESDOM.xlsx]

              (ooxml, embedded labels, table is [RAW EXTRACT]);

               

               

               

               

               

              Order:

              LOAD

                  "date",

                  party_name,

                  di_qty,

                  rate_per_mt,

                  disp_qty,

                  pending_qty

              FROM [lib://Sales/Bookings Sales.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

               

               

              as per your suggestion i did is:-

               

              Master:

              LOAD

                  factory,

                  "date",

                  party_no,

                  party_name,

                  cics,

                  category,

                  market,

                  im_code,

                  im_descr,

                  qty,

                  "rate",

                  basicvalue

                

               

              FROM [lib://Sales/SALESDOM.xlsx]

              (ooxml, embedded labels, table is [RAW EXTRACT]);

               

              Load

                   party_name,category,market,im_code,im_descr,qty,"rate",basicvalue,city,state,region,continent,party_name & '|' & date as Key

              FROM [lib://Sales/SALESDOM.xlsx]

              (ooxml, embedded labels, table is [RAW EXTRACT]);

               

               

              Order:

              LOAD

                  "date",

                  party_name,

                  di_qty,

                  rate_per_mt,

                  disp_qty,

                  pending_qty

              FROM [lib://Sales/Bookings Sales.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

               

              load

               

               

                  party_name,di_qty, date, rate_per_mt, pending_qty, party_name & '|' & date as Key

                 

                  FROM [lib://Sales/Bookings Sales.xlsx]

              (ooxml, embedded labels, table is Sheet1);

                 

                 

              Link_Table:

              Load DISTINCT Key, category,market,im_code,im_descr,qty,"rate",basicvalue,city,state,region,continent, Today() resident Master;

              Concatenate(Link_Table)

              Load DISTINCT Key, di_qty, date, rate_per_mt, pending_qty resident Order;

               

               

               

               

               

               

              Drop Fields party_name, date from Master1;

              Drop Fields party_name, date from Order;

               

              still getting error

            • Re: link different table with same fields
              neha shirsath

              See the code in attached file.

                • Re: link different table with same fields
                  Agrim Sharma

                  Hi Neha, sorry to say but i have Qlik Sense not Qlik View, can you share it in a note pad?

                  Thanks

                    • Re: link different table with same fields
                      neha shirsath

                      SALES:

                      LOAD party_name & '|' & date as Key,

                        party_name,

                           sales_amount,

                           date

                      FROM

                      [2months.xlsx]

                      (ooxml, embedded labels, table is Sheet3);

                       

                       

                      ORDER:

                      LOAD party_name & '|' & date as Key,

                        party_name,

                           booking_amount,

                           date

                      FROM

                      [2months.xlsx]

                      (ooxml, embedded labels, table is Sheet4);

                       

                       

                       

                       

                      LINK_TABLE:

                      LOAD party_name & '|' & date as Key,

                        party_name,

                        date

                      Resident SALES;

                      DROP Fields party_name, date From SALES;

                       

                       

                      Concatenate

                      LOAD party_name & '|' & date as Key,

                        party_name,

                        date

                      Resident ORDER;

                      DROP Fields party_name, date From ORDER;

                       

                       

                       

                       

                      Master_Cal:

                      Load distinct date  ,

                           //Floor(Claim_Process_Date) as DateNum ,

                           QuarterStart(date) as QuaterStartDate,

                        Day(date) as Day,

                        Month(date) as Month,

                        Year(date) as Year,

                        Monthname(date) as MonthYear,

                        num(month(date)) as Month_Number,

                        Week(date) as Week,

                        Month(date) &'-'& Year(date) as YearMonth ,

                      if(num(month(date))>=4 and num(month(date))<=6,'Q1',

                      if(num(month(date))>=7 and num(month(date))<=9,'Q2',

                      if(num(month(date))>=10 and num(month(date))<=12,'Q3',

                      if(num(month(date))>=1 and num(month(date))<=3,'Q4')))) as Quarter,

                      if(num(month(date))<=3,Year(date)-1&'-'&right(Year(date),2),Year(date)&'-'&right(year(date)+1,2)) as FinancialYear,

                      if(num(month(date))<=3,num(month(date))+9,num(month(date))-3) as FinancialMonthNumber

                      Resident LINK_TABLE;

                  • Re: link different table with same fields
                    Ted Spaete

                    A little different approach.  You wanted to group your results by Quarters.  Join the two tables and create a column that calculates the Quarter period based on either the Sales Date or Booking Date of the row.  Then Pivot with Party name and Quarter Number with Sums for Sales & Bookings.

                    • Re: link different table with same fields
                      Agrim Sharma

                      finally i did it as follow:-

                       

                      thanks all for kind help:)

                       

                       

                      map_market:

                      MAPPING LOAD DISTINCT

                          party_name,

                          market  

                      FROM [lib://Sales/SALESDOM.xlsx]

                      (ooxml, embedded labels, table is [RAW EXTRACT]);

                       

                      map_im_descr:

                      MAPPING LOAD DISTINCT

                          party_name,

                          im_descr  

                      FROM [lib://Sales/SALESDOM.xlsx]

                      (ooxml, embedded labels, table is [RAW EXTRACT]);

                       

                      SALES_VGM:

                      Concatenate Master:

                      LOAD

                          'Orders' as line_type,

                          "date",

                          party_name,

                         

                          APPLYMAP('map_market', party_name, 'Unknown') as market,

                          //APPLYMAP('map_im_descr', party_name, 'Unknown') as im_descr,

                          product as im_descr,

                          APPLYMAP('map_im_descr_limitation', product, 1) as im_descr_limited_flag,

                          APPLYMAP('map_product_master_name', product, 'Unknown') as product_code,

                          di_qty,

                          rate_per_mt,

                          disp_qty,

                          pending_qty,

                          pending_qty as qty

                      FROM [lib://Sales/Bookings Sales.xlsx]

                      (ooxml, embedded labels, table is Sheet1);