Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I'm new in Qlikview, I need a suggestion about loading two tables with one-to- many relationship and obtain in output one table with no rows duplicated and new coloumns.I have a table A of customers and table B of orders of only two products. I need an output table with two new columns in order to indicate if a customer has purchased ProductA, ProductB or both.
Table A: Costumers
ID_customer | CustomerName | Address |
---|---|---|
1 | Customer1 | Address1 |
2 | Customer2 | Address2 |
3 | Customer3 | Address3 |
Table 2: Orders
ID_customer | ID_Product | Date of purchase |
---|---|---|
1 | ProductA | 20/03/2017 09.00 |
1 | ProductA | 25/03/2017 09.35 |
1 | ProductB | 24/03/2017 17.38 |
2 | ProductA | 09/03/2017 15.45 |
output:
ID_customer | CustomerName | Address1 | ProductA | ProductB |
---|---|---|---|---|
1 | Customer1 | Address1 | Y | Y |
2 | Customer2 | Address2 | Y | N |
3 | Customer3 | Address3 | N | N |
Thank you for your advice
Hi,
Try this
T1:
LOAD ID_customer,
CustomerName,
Address
FROM
(ooxml, embedded labels, table is Sheet1);
Left Join
T2:
LOAD ID_customer,
ID_Product,
[Date of purchase]
FROM
(ooxml, embedded labels, table is Sheet2);
T3:
LOAD
ID_customer,
CustomerName ,
Address ,
ID_Product,
If(ID_Product = 'ProductA','Y','N' ) as ProductA,
If(ID_Product = 'ProductB' ,'Y','N') as ProductB,
[Date of purchase]
Resident T1;
DROP Table T1;
Hi Stephen,
thank you for your feedback.
If I run your code I obtain duplicated rows in T3 table.
For example
ID_customer | CustomerName | Address1 | ProductA | ProductB |
---|---|---|---|---|
1 | Customer1 | Address1 | Y | N |
1 | Customer1 | Address1 | N | Y |
Is there a way to merge the rows?
Thanks,
Flavio
Hi,
The output table shows the data of the customer's purchase on different dates.
Since A has purchased 3 products on three different dates, data related to A's purchase has appeared.
It is not duplicated.
Hello,
yes you are right, but is there a way to aggregate the data ? I'd like only to know if a costumer has ever purchased a kind of product so the output table should be for example for costumer 1
1 | Customer1 | Address1 | Y | Y |
Thanks in advance,
Flavio