List of Products Not Bought

[azmeerrehan]

I have a data set with fields Customer, Product and Sales. Now I want the table with All Customers showing only the Products they  never bought.

Attached please find the sample.

Please help me how can I Accomplish this task. I tried creating a  temp table but I  have so many products and customers that it run out of memory. Below is my Code in the script

Item_History_Temp:

lOAD *,

1 AS Flag;

LOAD

   Customer

   Product

FROM [$(vPathName)/ItemHistory.xlsx]

Temp:

LOAD DISTINCT  Product as AllItems

Resident Item_History_Temp;

Left Join(Temp)

LOAD Distinct Customer

Resident Item_History_Temp;

Join (Item_History_Temp)

LOAD Customer

  AllItems as Items

Resident Temp;

Item_History:

LOAD Customer,

If(Flag = 1, Items) as [Items Bought],

If(Flag <> 1, Items) as [Items Not Bought]

Resident Item_History_Temp

Group By Customer;

DROP Tables Temp, Item_History_Temp;

[neelamsaroha157]

Hi check this out. It might not be the cleanest solutions but it works fine.

[neelamsaroha157]

add the flag in the backend as well if you want to that -

[azmeerrehan]

The initial one I sent was just a simple table. I have an actual data model where I am looking to list all the products not bought by a customer.

Attached is my actual Data Model. How can I accomplish the same task in my data model. Attached are the sample XLS and qvw being used.

My data set is huge so I cant  do any aggregation on the front end.