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

[#2]

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

[sunny_talwar]

Can you elaborate the bar chart requirement?

[#4]

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

[sunny_talwar]

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?

[#6]

Dear Sunny,

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

Best Regards,

Kyaw Myo Tun

[sunny_talwar · Accepted solution]

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)

[#8]

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

[sunny_talwar]

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