Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table:
Product | Metric | Jan17 | Feb17 | Mar17 | Apr17 | May17 | Jun17 |
---|---|---|---|---|---|---|---|
P1 | Value | 1 | 8 | 3 | 6 | 2 | 1 |
P1 | Price | 3 | 5 | 4 | 8 | 5 | 3 |
P2 | Value | 4 | 4 | 65 | 4 | 8 | 5 |
P2 | Price | 6 | 5 | 98 | 3 | 7 | 7 |
What I need is to transpose the table to have something like this:
Product | Period | Value | Price |
---|---|---|---|
P1 | Jan17 | 1 | 3 |
P1 | feb-17 | 8 | 5 |
P1 | mar-17 | 3 | 4 |
P1 | Apr17 | 6 | 8 |
P1 | may-17 | 2 | 5 |
P1 | jun-17 | 1 | 3 |
P2 | Jan17 | 4 | 6 |
P2 | feb-17 | 4 | 5 |
P2 | mar-17 | 65 | 98 |
P2 | Apr17 | 4 | 3 |
P2 | may-17 | 8 | 7 |
P2 | jun-17 | 5 | 7 |
Do you know how could I do that?
I need to do it in script!!! When I load the table.
Thank you!
Try this
Table:
CrossTable(Month, Value, 2)
LOAD * INLINE [
Product, Metric, Jan17, Feb17, Mar17, Apr17, May17, Jun17
P1, Value, 1, 8, 3, 6, 2, 1
P1, Price, 3, 5, 4, 8, 5, 3
P2, Value, 4, 4, 65, 4, 8, 5
P2, Price, 6, 5, 98, 3, 7, 7
];
FinalTable:
LOAD DISTINCT Product,
Date(Date#(Month, 'MMMYY'), 'MMMYY') as Month
Resident Table;
FOR i = 1 to FieldValueCount('Metric')
LET vMetric = FieldValue('Metric', $(i));
Left Join (FinalTable)
LOAD Product,
Date(Date#(Month, 'MMMYY'), 'MMMYY') as Month,
Value as [$(vMetric)]
Resident Table
Where Metric = '$(vMetric)';
NEXT i
DROP Table Table;
Take a look here: The Crosstable Load
- Marcus
Try this
Table:
CrossTable(Month, Value, 2)
LOAD * INLINE [
Product, Metric, Jan17, Feb17, Mar17, Apr17, May17, Jun17
P1, Value, 1, 8, 3, 6, 2, 1
P1, Price, 3, 5, 4, 8, 5, 3
P2, Value, 4, 4, 65, 4, 8, 5
P2, Price, 6, 5, 98, 3, 7, 7
];
FinalTable:
LOAD DISTINCT Product,
Date(Date#(Month, 'MMMYY'), 'MMMYY') as Month
Resident Table;
FOR i = 1 to FieldValueCount('Metric')
LET vMetric = FieldValue('Metric', $(i));
Left Join (FinalTable)
LOAD Product,
Date(Date#(Month, 'MMMYY'), 'MMMYY') as Month,
Value as [$(vMetric)]
Resident Table
Where Metric = '$(vMetric)';
NEXT i
DROP Table Table;
Hi,
Try:
Data:
CrossTable(Period, Value, 2)
LOAD Product,
Metric,
Jan17,
Feb17,
Mar17,
Apr17,
May17,
Jun17
FROM
[https://community.qlik.com/thread/272208]
(html, codepage is 1252, embedded labels, table is @1)
Where Metric = 'Value';
Drop Field Metric;
JoinData:
CrossTable(Period, Price, 2)
LOAD Product,
Metric,
Jan17,
Feb17,
Mar17,
Apr17,
May17,
Jun17
FROM
[https://community.qlik.com/thread/272208]
(html, codepage is 1252, embedded labels, table is @1)
Where Metric = 'Price';
Drop Field Metric;
Left Join(Data) LOAD * Resident JoinData;
Drop Table JoinData;
to get:
Product | Period | Value | Price |
---|---|---|---|
P1 | Jan17 | 1 | 3 |
P1 | Feb17 | 8 | 5 |
P1 | Mar17 | 3 | 4 |
P1 | Apr17 | 6 | 8 |
P1 | May17 | 2 | 5 |
P1 | Jun17 | 1 | 3 |
P2 | Jan17 | 4 | 6 |
P2 | Feb17 | 4 | 5 |
P2 | Mar17 | 65 | 98 |
P2 | Apr17 | 4 | 3 |
P2 | May17 | 8 | 7 |
P2 | Jun17 | 5 | 7 |
Cheers
Andrew
Hello Sunny,
Thanks for your answer. A couple of questions:
1 - When you say CrossTable(Month, Value, 2), Value is the text from Metric column or is it just a random name?
2 - If I have 5 Metrics (Value, Price, Other 1, Other 2, Other 3) do I have to specify them? If they have a space in the name (like Other 1), do I use single quotes?
Thanks again!
1) Random Name
2) Nothing needs to change
It worked! Thank you!!!