Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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???
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
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
Would you be able to share the actual formatted sample excel file with few lines of data in it?
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
Dear Sunny,
Here are some real data. Please see below.
Table1:
Table2:
Hope to help me.
Best Regards,
Kyaw Myo Tun
Hi
Please provide some sample in excel .
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 |
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: