topic Specific crosstable in QlikView

Specific crosstable

Mon, 02 Aug 2010 20:00:51 GMT

I have a table with this structure :

Name of 3 products sell to client, with amout & quantity

Id	Prod1	Sales1	Q1	Prod2	Sales2	Q2	Prod3	Sales3	Q3
1	A	100	10	B	78	12	A	56	4
2	B	12	1	C	34	7	H	56	2
3	X	32	5	U	56	3	C	24	6

How can I get this new structure more useful in QV :

Id	Product	Sales	Quantity
1	A	100	10
1	B	78	12
1	A	56	4
2	B	12	1
2	C	34	7
2	H	56	2
3	X	32	5
3	U	56	3
3	C	24	6

Thanks for your help

JJJ

Specific crosstable

johnw — Mon, 02 Aug 2010 20:48:41 GMT

Here's one way:

Raw:
LOAD * INLINE [
Id Prod1 Sales1 Q1 Prod2 Sales2 Q2 Prod3 Sales3 Q3
1 A 100 10 B 78 12 A 56 4
2 B 12 1 C 34 7 H 56 2
3 X 32 5 U 56 3 C 24 6
] (delimiter is ' ');

Products:
CROSSTABLE (Row, Product)
LOAD Id, Prod1 as "1", Prod2 as "2", Prod3 as "3"
RESIDENT Raw;
Sales:
CROSSTABLE (Row, Sales)
LOAD Id, Sales1 as "1", Sales2 as "2", Sales3 as "3"
RESIDENT Raw;
Quantities:
CROSSTABLE (Row, Quantity)
LOAD Id, Q1 as "1", Q2 as "2", Q3 as "3"
RESIDENT Raw;

Final:
NOCONCATENATE LOAD * RESIDENT Products;
LEFT JOIN (Final) LOAD * RESIDENT Sales;
LEFT JOIN (Final) LOAD * RESIDENT Quantities;

DROP TABLES Products, Sales, Quantities;
DROP FIELD Row;

Specific crosstable

Mon, 02 Aug 2010 21:45:07 GMT

Thanks John for your time.

Well done, 3 crosstable and a left join

jjj