topic Re: Basket Cross table in QlikView

Basket Cross table

Mon, 26 Nov 2012 12:26:34 GMT

Hi Experts ,

I'm a begginer on QV and I'm looking for a solution about a basket customer analysis like this

I have quantities sold of 4 items (or Item Groups) for several customers (or Cust Groups):

Data set

and the target result should be a cross table built as sum qty of both items if and only if they are sold together

Cross table

that is

How can build a matrix like this ?

Thanks in advance

Gysbert_Wassenaar — Mon, 26 Nov 2012 12:46:42 GMT

See attached example

swuehl — Mon, 26 Nov 2012 13:01:15 GMT

A slightly different approach:

TMP:

CROSSTABLE (Item, Value,2) LOAD recno() as ID, * INLINE [

Customer, Item A, Item B, Item C, Item D

Customer 1, 50, 42, 5, 0

Customer 2, 120, 0, 40, 0

Customer 3, 0, 65, 0, 32

Customer 4, 0, 10, 40, 121

Customer 5, 0, 0 ,50 ,130

Customer 6 ,0, 5 ,1, 0

];

BASKET:

NOCONCATENATE LOAD * Resident TMP where Value >0;

LOAD ID, Item as Item2, Value as Value2 Resident TMP where Value >0;

drop table TMP;

Then create a pivot with dimensions Item and Item2 and as expresssion:

=if (Item <> Item2,rangesum(sum(Value),sum(Value2)))

Regards,

Stefan

Mon, 26 Nov 2012 15:03:08 GMT

Thanks a lot

Gysberth & Swuehl

for your suggestions !!!

Regards