topic Re: How to join two tables by different fields? in QlikView

How to join two tables by different fields?

Fri, 07 Aug 2015 03:54:25 GMT

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

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 03:59:08 GMT

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???

Re: How to join two tables by different fields?

PrashantSangle — Fri, 07 Aug 2015 04:02:04 GMT

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

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 04:04:03 GMT

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

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 04:31:58 GMT

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

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 05:02:58 GMT

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

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 05:14:11 GMT

Dear Sunny,

Here are some real data. Please see below.

Table1:

Table2:

Hope to help me.

Best Regards,

Kyaw Myo Tun

Re: How to join two tables by different fields?

MayilVahanan — Fri, 07 Aug 2015 05:24:27 GMT

Please provide some sample in excel .

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 05:25:49 GMT

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

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 05:40:34 GMT

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:

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 06:49:26 GMT

Dear Sunny,

It worked correctly what I want to but I have one thing left to solve is to show by bar chart with Ac_No(20640906004074901, not with 330601017 belongs to which Department). I tested with some aggregation functions on bar chart but it didn't work correctly. Hope to help me again.

Best Regards,

Kyaw Myo Tun

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 06:51:28 GMT

Can you elaborate the bar chart requirement?

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 07:01:01 GMT

Dear Sunny,

I would like to show which Ac_No belongs to which Department. I hope you to clear my requirement.

Best Regards,

Kyaw Myo Tun

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 07:05:02 GMT

206000015180001 is Reference_No and 20611207201101401 is Ac_No. I am not sure what you are looking for here. Do you want Reference_No and count of Department on the bar chart?

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 07:22:04 GMT

Dear Sunny,

Sorry for my mistake. I only want to show with Ac_No.

Best Regards,

Kyaw Myo Tun

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 07:29:39 GMT

This?

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)

Re: How to join two tables by different fields?

Fri, 07 Aug 2015 10:03:45 GMT

Dear Sunny,

One more question please. I also have another condition. These are between Ac_No and Product only.

Firstly, We filtered by Reference_No and Ac_No and one more condition for Product now.(for exp: I've maintained Product - CQWL = Cash Withdrawl(Department) in other spread sheet. I also want to show that in my bar chart. Hope to help me.

Best Regards,

Kyaw Myo Tun

Re: How to join two tables by different fields?

sunny_talwar — Fri, 07 Aug 2015 13:19:37 GMT

Not sure I understand what you are trying to do. You will need to share more information. I would suggest closing this thread down and probably starting a new thread for your new requirement. This way more people will try and provide your assistance.

Best,

Sunny