Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Transpose Table

Hello,

I have the following table:

ProductMetricJan17Feb17Mar17Apr17May17Jun17
P1Value183621
P1Price354853
P2Value4465485
P2Price6598377

What I need is to transpose the table to have something like this:

ProductPeriodValuePrice
P1Jan1713
P1feb-1785
P1mar-1734
P1Apr1768
P1may-1725
P1jun-1713
P2Jan1746
P2feb-1745
P2mar-176598
P2Apr1743
P2may-1787
P2jun-1757

Do you know how could I do that?

I need to do it in script!!! When I load the table.

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
marcus_sommer

Take a look here: The Crosstable Load

- Marcus

sunny_talwar

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;

effinty2112
Master
Master

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
P1Jan1713
P1Feb1785
P1Mar1734
P1Apr1768
P1May1725
P1Jun1713
P2Jan1746
P2Feb1745
P2Mar176598
P2Apr1743
P2May1787
P2Jun1757


Cheers


Andrew

microwin88x
Creator III
Creator III
Author

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!

sunny_talwar

1) Random Name

2) Nothing needs to change

microwin88x
Creator III
Creator III
Author

It worked! Thank you!!!