Basket Cross table

[#1]

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

Sum of qty	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

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

summary of both items sold	Item A	Item B	Item C	Item D
Item A	0	92	215	0
Item B	92	0	103	228
Item C	215	103	0	341
Item D	0	228	341	0

that is

Item A+B	92
Item A+C	215
Item A+D	0
Item B+C	103
Item B+D	228
Item C+D	341

How can build a matrix like this ?

Thanks in advance

[Gysbert_Wassenaar]

See attached example

[swuehl]

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

[#4]

Thanks a lot

Gysberth & Swuehl

for your suggestions !!!

Regards