topic Transpose Table in QlikView

Transpose Table

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Transpose Table

marcus_sommer — Wed, 23 Aug 2017 15:24:31 GMT

Take a look here: The Crosstable Load

- Marcus

Re: Transpose Table

sunny_talwar — Wed, 23 Aug 2017 15:33:59 GMT

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;

Re: Transpose Table

effinty2112 — Wed, 23 Aug 2017 15:39:41 GMT

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

Re: Transpose Table

microwin88x — Wed, 23 Aug 2017 16:03:21 GMT

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!

Re: Transpose Table

sunny_talwar — Wed, 23 Aug 2017 16:06:09 GMT

1) Random Name

2) Nothing needs to change

Re: Transpose Table

microwin88x — Wed, 23 Aug 2017 16:21:07 GMT

It worked! Thank you!!!