Map two tables and filter data during Data Load Editor

[andymanu]

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_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:

Customer_ID	Cust_Name	Country
C100	Amal	NZ
C102	Kamal	AUS
C104	Nayan
C105	Bimal	NZ
C106	Paul	SL

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

[andymanu]

Hi,

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

Apologies in advance.

Regards,

Andy

[annu181818]

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]

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]

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]

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]

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]

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]

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;