Buying Patterns : Who hasn't bought from you!

[#1]

Hi

Please assist where possible:

I want a calculation that will show me who has not purchased a product yet ex.

You have a Debtor and you have a product description.

Debtors: 3J's, Dischem, Paly Red , House and home for example. (Look at table A)

Products: 1, 2, 3, 4, 5, 6, 7, 8

3J's has purchased products 1, 3, 6 and 7

Dischem has purchased products 2, 3 and 7

Paly Red has purchased products 2, 4 and 7

House and home has purchased products 4 and 5

So here I made a table: (This is who has bought: "Products purchased by Debtor")

Table A:

Debtor	Product	Purchase date (DD/MM/YYYY)
3J's	1	12/2/2009
3J's	3	4/3/2010
3J's	6	7/6/2010
3J's	7	4/11/2010
Dischem	2	4/12/2010
Dischem	3	2/5/2010
Dischem	7	5/9/2009
Paly Red	2	6/7/2009
Paly Red	4	8/3/2010
Paly Red	7	8/4/2010
House and home	4	4/11/2009
House and home	5	12/12/2010

Now I want a calculation that will be able to give me who hasn't purchased a product. Like: (Look at table B)

3J's Has not yet purchased products 2,3,5 and 8

Dischem Has not yet purchased products 1,4,5,6 and 8

Paly Red Has not yet purchased products 1,3,5,6 and 8

House and home Has not yet purchased products 1,2,3,6,7 and 8

So I made another table: (This is who has not bought a product: "Products not yet purchased by a Debtor")

Table B:

Debtor	Product Not Purchased
3J's	2
3J's	3
3J's	5
3J's	8
Dischem	1
Dischem	4
Dischem	5
Dischem	6
Dischem	8
Paly Red	1
Paly Red	3
Paly Red	5
Paly Red	6
Paly Red	8
House And Home	1
House And Home	2
House And Home	3
House And Home	6
House And Home	7
House And Home	8

I need a calculation to return Table B (I need the Calculation for QV 8.5 as well as QV9 SR 3 )

Please assist me where possible.

Thanks,

[#2]

If your data originally comes from a SQL table source, you could use the SQL set operator EXCEPT, if your database supports it.

i.e. select * from all_products_and_customers except select * from sales.

But if you have to do it in qlikview, I don't think qlikview has except or intersect operations, so you can achieve a similar result with a left join. See example below. You will need a table of all products joined with all customers, from which you subtract the sold products. The creation of the temp table from the left join is technically not necessary, but does make the example easiesr to follow. The fields in the cartesian product and unsold products tables are renamed to avoid qlikview creating undesirable connections between sales and unsold products.

products:
load * inline
[
prodcode, desc
p1,blah
p2,blah
p3,blah
]
;
sales:
load * inline
[
customer,prodcode,qty
c1,p1,1
c1,p2,2
c2,p3,3
]
;
allcust:
Load distinct customer resident sales
;
cartesian_product:
load prodcode as c_prodcode resident products;
inner join load customer as c_customer resident allcust;

temp:
load
 c_prodcode as unsold_products.prodcode
 ,c_customer as unsold_products.customer
resident cartesian_product;
left join
load
 prodcode as unsold_products.prodcode
,prodcode as zprodcode
,customer as unsold_products.customer
,customer as zcustomer
resident sales
;
unsold_products:
load
 unsold_products.prodcode
 ,unsold_products.customer
resident temp
where isnull(zprodcode)
;
drop table temp;