Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to join two tables by different fields?

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

1 Solution

Accepted Solutions
sunny_talwar

This?

Capture.PNG

Script:

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, Flag

330601017, Deposit, 1

330601018, Remittance, 1

];

Left Join(Table)

LOAD Reference_No,

  Department1 as Department

Resident Table

Where not IsNull(Department1);

DROP Field Department1;

FinalTable:

NoConcatenate

LOAD Ac_No,

  Reference_No,

  Department,

  If(Len(Trim(Flag)) = 0, 0, Flag) as Flag

Resident Table;

DROP Table Table;

Chart's Dimension:  Ac_No

Expression: =Count({<Flag -= {'1'}>}Department)

View solution in original post

17 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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 .

Thanks & Regards,
Mayil Vahanan R
sunny_talwar

Is this the output you are looking for?

Reference_No

Ac_NoDepartment
206000015181000120640906004074901Deposit
206000015181000120666906001101401Deposit
2060000151810001330601017Deposit
206000025181000120611207201101401Deposit
2060000251810001330601017Deposit
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:

Capture.PNG