Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andymanu
Creator II
Creator II

Map two tables and filter data during Data Load Editor

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

8 Replies
andymanu
Creator II
Creator II
Author

Hi,

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

Apologies in advance.

Regards,

Andy

annu181818
Contributor II
Contributor II

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

shiveshsingh
Master
Master

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;

andymanu
Creator II
Creator II
Author

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

andymanu
Creator II
Creator II
Author

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

annu181818
Contributor II
Contributor II

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

andymanu
Creator II
Creator II
Author

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

shiveshsingh
Master
Master

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;