Products Never Bought By a Customer

[azmeerrehan]

I have attached  the sample qvw with the Data Model and the 2 Sample Excel Files. I have a Sales table and Product Table. They are linked by the Inv Date and Order Number. I want to find out for each customer which Product they never bought. SO Basically I am looking for a straight table with Customer name, Product they never bought, Product Cat and Product Sub Category.

Please be aware that my data set is huge and cant do any Aggregation on the front end. Ideally would like to generate a field call "Products Not Bought" in the script.

Please let me know if anyone has a solution for this issue.

[lironbaram · Accepted solution]

hi

have a look at the attach example

hope it's helps you

[azmeerrehan]

Works fine except for one big issue. If I select a Product from "Product" field I get the correct Product Category and Product  Sub Category , and  it selects every possible Product from "ProductNotSold" field .  It should be giving me the same Product from the "ProductNotSold" field  what I select from the Product field.

Please let me know if we can fix this. Please see the attached screen shot for the issue

[lironbaram]

hi

you can connect to table of products not bought either by customer like in the example

or by product that will answer your question

but you can't connect it by both of them unless you'll have both product and customer in the same table

[captain89]

//Hi, see below:

//Sales table is useless, if you need customer_name you may use applymap:

Sales:

LOAD Cust_Code,

     Year(Inv_Date) as Year,

     Inv_Date & '|' & Order_Number as Key,

     Order_Number,

     Inv_Date,

     Cust_Name

FROM

[Sales_Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

map_custumer:

mapping load

Key, Cust_Name resident Sales;

//and then drop sales table... in this way you have only one fact table

Drop Table Sales;

Fact_Table:

LOAD ApplyMap('map_custumer', Inv_Date & '|' & Order_Number, '') as Cust_Name,

     Cust_Code,

     Inv_Date & '|' & Order_Number as Key,

     text(Cust_Code)&text(Product) as Key2,

     Year(Inv_Date) as Year,

     Product,

     Product_Cat,

     Product_Sub_Cat,

     Sales

FROM

[Item_Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

//find the possible pairs of product-customer like this

NeverBoughtW:

load distinct

text(Product) as ProductW

resident Fact_Table;

join

load distinct

text(Cust_Code) as Cust_CodeW resident Fact_Table;

//then map bought ones

map_bought:

Mapping load

text(Cust_Code)&text(Product), 'Y' resident Fact_Table;

//here you have the list of never bought and bought codes

Items:

load

text(Cust_CodeW)&text(ProductW) as Key2,

ProductW,

Cust_CodeW,

Applymap('map_bought', text(Cust_CodeW)&text(ProductW), 'N') as Bought?

resident  NeverBoughtW;

Drop table NeverBoughtW;