one to many relationship - distinct output

[#1]

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

[stephenedberkg]

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;

[#3]

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

[#4]

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.

[#5]

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