8 Replies Latest reply: Jun 12, 2018 6:52 AM by Shivesh Singh RSS

    Map two tables and filter data during Data Load Editor

    Andy Manuja

      Hi,

      I am trying to link two tables using Map function.

      The unique key columns content the exactly same data however, the field names are different (Ex- Customer_ID and Cust_ID).

      Below find the two tables which I have used in my script,

       

      Sales_Table:

         

      Sales_IDSales_PersonSales_DateProductQuantityCust_ID
      S100Amal10/03/2018A10C100
      S101Kamal15/02/2018B25C102
      S102Nayan16/04/2018C10C104
      S103Bimal10/04/2018D25C105
      S104Syril5/02/2018G20C102
      S105Paul13/04/2018B18C106
      S106Kevin15/04/2018A10C104
      S107Mike10/05/2018C15

      C102

       

      Customer_Table:

       

       

      Customer_IDCust_NameCountry
      C100AmalNZ
      C102KamalAUS
      C104Nayan
      C105BimalNZ
      C106PaulSL

       

      My objective is to merge the two tables using ApplyMap function instead of Join function and need to filter the data based on some parameters. For ex, Country is equals to 'NZ' and 'SL', Quantity is greater than 15 units.

       

      Please find the script I have written:

       

      Sales:

      LOAD

          Sales_ID,

          Sales_Person,

          Sales_Date,

          Product,

          Quantity,

          Cust_ID as Customer_ID

      FROM [lib://Maps/Slaes.xlsx]

      (ooxml, embedded labels, table is Sales);

       

      Customer:

      LOAD

          Customer_ID,

          Cust_Name,

          Country

      FROM [lib://Maps/Customer.xlsx]

      (ooxml, embedded labels, table is Customer);

       

      MapCustomer_IDtoCountry:

      Mapping Load Customer_ID,Country

      Resident Customer;

      Load *,

      ApplyMap('MapCustomer_IDtoCountry',Customer_ID, 'No Country') as Country

      Resident Sales;

       

       

      Above describes the mapping of the two tables.  I wrote a script as mentioned below to filter the required data creating a temporary table:

       

      Temp_Table:

      Load *

      Resident Sales;

       

      Left Join (Customer)

      Load *,

      1 as Flag,Flag

      Resident Temp_Table

      Where Quantity > 15;

       

      Drop Table Sales;

      Drop Table Customer;

       

      Drop Table MapCustomer_IDtoCountry;

       

      I am getting an error stating that, the "Temp_Table" was not found?

       

      Appreciate of you could suggest me an efficient method to accomplish the above said task soon as possible using the ApplyMap function.

       

      Note,

      I don't want to have Synthetic Keys and would be great if I could have only a single table at last with all the required data.

       

      Thanking you in advance.\

      Regards,

       

      Andy

        • Re: Map two tables and filter data during Data Load Editor
          Andy Manuja

          Hi,

          Mistakenly I have put two "Flag" statements under the left join function.

           

          Apologies in advance.

           

          Regards,

          Andy

            • Re: Map two tables and filter data during Data Load Editor
              Shivesh Singh

              hi

               

              try this

               

              Sales_Table:

              LOAD * INLINE [

                  Sales_ID, Sales_Person, Sales_Date, Product, Quantity, Cust_ID

                  S100, Amal, 10/03/2018, A, 10,C100

                  S101, Kamal, 15/02/2018, B, 25,C102

                  S102, Nayan, 16/04/2018, C, 10,C104

                  S103, Bimal, 10/04/2018, D, 25,C105

                  S104, Syril, 5/02/2018, G, 20,C102

                  S105, Paul, 13/04/2018, B, 18,C106

                  S106, Kevin, 15/04/2018, A, 10,C104

                  S107, Mike, 10/05/2018, C, 15,C102

                 

              ];

               

               

              Customer_Table:load * Inline [

              Customer_ID,Cust_Name,Country

              C100,Amal,NZ

              C102,Kamal,AUS

              C104,Nayan,

              C105,Bimal,NZ

              C106,Paul,SL];

               

               

              MapCustomer_IDtoCountry: Mapping Load Customer_ID,Country

               

               

              Resident Customer_Table;

               

               

               

               

              Temp:Load *,

               

               

              ApplyMap('MapCustomer_IDtoCountry',Cust_ID, 'No Country') as Country

               

               

              Resident Sales_Table;

               

               

              Left Join (Customer_Table)

               

               

              Load *,

               

               

              1 as Flag

               

               

              Resident Temp

               

               

              Where Quantity > 15;

              drop tables Sales_Table,Customer_Table;

                • Re: Map two tables and filter data during Data Load Editor
                  Andy Manuja

                  Hi Shivesh,

                  Thank you for the help.

                   

                  However, the script does not filter the required data (ex- Quantity >15, etc.)

                   

                  Currently I am working on that and will update if I could successfully do it.

                   

                  Thank you.

                   

                  Kind regards,


                  Andrew

                    • Re: Map two tables and filter data during Data Load Editor
                      Shivesh Singh

                      Hi

                       

                      Please try below script, it will filter your 15 quantity.

                       

                      Sales_Table:

                      LOAD * INLINE [

                          Sales_ID, Sales_Person, Sales_Date, Product, Quantity, Cust_ID

                          S100, Amal, 10/03/2018, A, 10,C100

                          S101, Kamal, 15/02/2018, B, 25,C102

                          S102, Nayan, 16/04/2018, C, 10,C104

                          S103, Bimal, 10/04/2018, D, 25,C105

                          S104, Syril, 5/02/2018, G, 20,C102

                          S105, Paul, 13/04/2018, B, 18,C106

                          S106, Kevin, 15/04/2018, A, 10,C104

                          S107, Mike, 10/05/2018, C, 15,C102

                         

                      ];

                       

                       

                      Customer_Table:load * Inline [

                      Customer_ID,Cust_Name,Country

                      C100,Amal,NZ

                      C102,Kamal,AUS

                      C104,Nayan,

                      C105,Bimal,NZ

                      C106,Paul,SL];

                       

                       

                      MapCustomer_IDtoCountry: Mapping Load Customer_ID,Country

                       

                       

                      Resident Customer_Table;

                      Temp:Load *,

                       

                       

                      ApplyMap('MapCustomer_IDtoCountry',Cust_ID, 'No Country') as Country

                       

                       

                      Resident Sales_Table

                      Where Quantity > '15';

                       

                       

                      Left Join (Customer_Table)

                       

                      Load *,

                      1 as Flag

                      Resident Temp;

                      Drop tables Sales_Table,Customer_Table;

                • Re: Map two tables and filter data during Data Load Editor
                  Anurag Saxena

                  Hi Andy,

                   

                  Frankly speaking I am not that clear about your requirement. Does that mean, you want to have all the fields/columns in one single table using one joining key, basis on few criteria/s ?
                  In that case Cust_ID will be the joining key between both the tables..!!

                   

                  As per my knowledge, there are three different ways to combine two tables in Qlik :

                  1) using applymap function

                  2) using Lookup function

                  3) using JOINs

                   

                  Let me know, If i got your requirements correct, I can help you in achieving same and If not please provide some more clarity on your exact requirement/s.

                   

                  Many Thanks..!!
                  Regards,

                  Anurag Saxena

                    • Re: Map two tables and filter data during Data Load Editor
                      Andy Manuja

                      Hi Anurag,

                       

                      Thank you very much for your consideration.

                       

                      Yes, I want to create a single table with all the fields and was using the "Customer_ID" as the joining key (Could use "Cust_ID" as well. Is there any advantage using the "Cust_ID" over "Customer_ID" ?)

                       

                      I was able to combined all the required fields and to develop a single temporary table. However, request your valuable help if required when it comes to filtering the data from the big table (Meaning removing unnecessary rows applying different conditions).

                       

                      Thank you very much.

                       

                      Kind regards,

                       

                      Andrew

                        • Re: Map two tables and filter data during Data Load Editor
                          Anurag Saxena

                          Hi Andrew,

                           

                          Yes, Cust_ID is an important field to have as it will act as a joining key in both tables. Also, if the column name is same, Qlik can easily identify joining key.

                           

                          Basis your requirement, I have found a way. Please go through the script below:

                           

                          ------------------------------------------------------------------------------------------------------------

                          ------------------------------------------------------------------------------------------------------------

                          Sales_Table_Raw:

                           

                          Load *

                          Inline [

                          Sales_ID, Sales_Person, Sales_Date, Product, Quantity, Cust_ID

                          S100, Amal, 10/03/2018, A, 10, C100

                          S101, Kamal, 15/02/2018, B, 25, C102

                          S102, Nayan, 16/04/2018, C, 10, C104

                          S103, Bimal, 10/04/2018, D, 25, C105

                          S104, Syril, 5/02/2018, G, 20, C102

                          S105, Paul, 13/04/2018, B, 18, C106

                          S106, Kevin, 15/04/2018, A, 10, C104

                          S107, Mike, 10/05/2018, C, 15, C102

                          ];

                           

                          NOCONCATENATE

                          Combined_Table:

                          Load Sales_ID,

                          Sales_Person,

                          Sales_Date,

                          Product,

                          Quantity,

                          Cust_ID                                                        // JOINING KEY in both tables

                          Resident Sales_Table_Raw

                          where "Quantity" > 10 ;                               // You can put any condition here on big table

                           

                          Drop table Sales_Table_Raw;

                           

                          left join(Combined_Table)

                          Customer_Table:

                          Load *

                          Inline [

                          Cust_ID, Cust_Name, Country

                          C100, Amal, NZ

                          C102, Kamal, AUS

                          C104, Nayan,

                          C105, Bimal, NZ

                          C106, Paul, SL

                          ];

                           

                          Finally the Combined_Table will be the single table containing data from both the tables basis your condition.

                          Let me know, if this work for you. We can try the same thing done by using Lookup function as well, but in that case extra rows would not be excluded.

                          Hope this helps.

                           

                          Thanks & Regards,

                          Anurag Saxena

                            • Re: Map two tables and filter data during Data Load Editor
                              Andy Manuja

                              Thanks Anurag,

                               

                              Actually, this is a dump data set I have created to test the code. I initially tried with the Left Join function and it worked. However, when it comes to my actual  large data set (about 140,000 records), the loading process takes more time.

                               

                              That is the reason why I thought of using the ApplyMap function just to add the required columns to  a single table.

                              I was able to create a single table with all the required data.

                               

                              I'll keep in touch with you.

                               

                              Kind regards,

                               

                              Andy