topic Re: Products Never Bought By a Customer in App Development

Products Never Bought By a Customer

azmeerrehan — Sun, 09 Sep 2018 00:07:54 GMT

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.

Re: Products Never Bought By a Customer

lironbaram — Sun, 09 Sep 2018 14:14:17 GMT

have a look at the attach example

hope it's helps you

Re: Products Never Bought By a Customer

azmeerrehan — Sun, 09 Sep 2018 18:48:57 GMT

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

Re: Products Never Bought By a Customer

lironbaram — Mon, 10 Sep 2018 13:00:46 GMT

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

Re: Products Never Bought By a Customer

captain89 — Mon, 10 Sep 2018 14:18:48 GMT

//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;