How to join two tables by different fields?

[#1]

Dear Experts,

                I would like to join two table for my analysis purpose. The main purpose is to identify "The customers' favorite products" for my shop. In below situation, Customer Id 111 favorite product is "Clothing" in my shop. In my source file, I can only get  "Customer Id" and "Product Id" altogether.I would like to know is "How to join these two tables" to get analysis form.Hope to help me.

        

Best Regards,

Kyaw Myo Tun

[sunny_talwar]

Table 2 looks like a good data to load into QlikView, but Table 1 seems out of order. Is this the exact way the data is structured or is this just to show what you are looking for???

[PrashantSangle]

Hi,

1: Create New Field in Table1 with the help of wildmatch() or match().

2: Name it as Product _Id

3: Then USe LEft Join to join the table1 an Table2 or Kept as it is so because of association it will map to each other

Try like,

Table1:

Load *,wildmatch(Customer ID,'AB*') as Product_ID from Table1;

Table2:

Load * from Table2;

Regards

[#4]

Dear Sunny,

           That is just to show what I am looking for and the two tables are in separate sheets. Then, you could neglect the "Date" field. Is it possible to get my requirement?

Thanks for your reply.

Best Regards,

Kyaw Myo Tun

[sunny_talwar]

Would you be able to share the actual formatted sample excel file with few lines of data in it?

[#6]

Dear Max,

      It showed like below and I would like to get new field in Table1:.

But I would like to get as below,

After that, I'll show Customer Id(for only 111,112,113,114 because I've maintain another excel file with Customer Id(111),Customer Name(Zaw Min Naing). at that situation, I don't want to get Customer Id(AB112,AB113,AB114,AB115).

Best Regards,

Kyaw Myo Tun

[#7]

Dear Sunny,

            Here are some real data. Please see below.

Table1:

Table2:

Hope to help me.

Best Regards,

Kyaw Myo Tun

[MayilVahanan]

Hi

Please provide some sample in excel .

[sunny_talwar]

Is this the output you are looking for?

Reference_No	Ac_No	Department
2060000151810001	20640906004074901	Deposit
2060000151810001	20666906001101401	Deposit
2060000151810001	330601017	Deposit
2060000251810001	20611207201101401	Deposit
2060000251810001	330601017	Deposit

[sunny_talwar]

Try this:

Table:

LOAD * Inline [

Reference_No, Ac_No

2060000151810001, 20640906004074901

2060000151810001, 20666906001101401

2060000151810001, 330601017

2060000251810001, 20611207201101401

2060000251810001, 330601018

];

Left Join(Table)

LOAD * Inline [

Ac_No, Department1

330601017, Deposit

330601018, Remittance

];

Left Join(Table)

LOAD Reference_No,

  Department1 as Department

Resident Table

Where not IsNull(Department1);

DROP Field Department1;

Output: