Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
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;
Thanks John for your time.
Well done, 3 crosstable and a left join
jjj