Products Never Bought

[azmeerrehan]

I have a customer data with sales for Products.   I have fields

Customer

Year

Product

Sales

I want to get the list of Products which were never bought by a customer

Lets say I have 3 Products and Customer A never bought Product no 3 so I want to see  out put as

Customer A , 3

Please Help

[azmeerrehan]

Can you pls attach a sample?

[ogautier62]

OK but later

For the moment I'm waiting for the dentist l've just my phone 😉

[azmeerrehan]

Sorry to bother you . I tried the second approach but it doesn't work

[ogautier62]

no problm Rehan,

here it is :

with these datas :

Customer	Year	Product	Sales
cust1	2018	A	35
cust1	2017	B	14
cust1	2018	C	10
cust2	2017	A	5
cust2	2018	B	40
cust3	2017	C	21
cust4

1)

custprod:

load distinct Customer resident sales;

tmp:

load distinct Product resident sales;

outer join (custprod) load Product resident tmp;

drop table tmp;

then in front :

2)

prod2:

load distinct Product as Product2 resident sales;

then in front :

regards

[azmeerrehan]

Can you pls attach the qvw?

[azmeerrehan]

The intitial 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

[azmeerrehan]

My data set is so huge that the charts times out . There has to be  a way to create these " Not Bought Products" as a field in the data set

[ogautier62]

Hi,

that's why solution 1 is better and should not increase much response time,

with your data , in script :

item:

LOAD  * FROM test.xlsx

(ooxml, embedded labels, table is item);

custprod:

load distinct Cust_Code resident item;

tmp:

load distinct Product resident item;

outer join (custprod) load Product resident tmp;

drop table tmp;

and then a sheet with just the two dimensions :

Cust_Code, Product

and as expression :

I've free desktop installed, I can't up or download qvw

regards

[ogautier62]

oups that was the wrong sheet, here is better :