topic Transforming rows into columns in QlikView

Transforming rows into columns

ali_hijazi — Fri, 20 Jan 2012 13:47:00 GMT

Hello everyone

I have a table in a qlikview document as follows:

TheValue TableName

-------------------------------------------------------------------------------------------------

x Colors.PropertyValueID

y Make.PropertyValueID

z Machinery.PropertyValueID

...

Now I want to transform the above table into the following structure:

TheValue Colors.PropertyValueID Make.PropertyValueID Machinery.PropertyValueID

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

x x NULL NULL

y NULL y NULL

z NULL NULL z

....

is this possible?

Please advise!!!

Transforming rows into columns

swuehl — Fri, 20 Jan 2012 14:04:53 GMT

Maybe like

TRANSFORM:

Generic LOAD *, TheValue as Value INLINE [

TheValue , TableName

x, Colors.PropertyValueID

y, Make.PropertyValueID

z, Machinery.PropertyValueID

];

A table box in the front end will show your expected result.

Transforming rows into columns

ali_hijazi — Fri, 20 Jan 2012 14:14:26 GMT

this gives me 3 different tables

I want them all in one table

Transforming rows into columns

swuehl — Fri, 20 Jan 2012 14:25:56 GMT

INPUT:

LOAD * INLINE [

TheValue , TableName

x, Colors.PropertyValueID

y, Make.PropertyValueID

z, Machinery.PropertyValueID

];

CROSS:

Generic LOAD *, TheValue as Value resident INPUT;

RESULT:

LOAD TheValue resident INPUT;

drop table INPUT;

left join (RESULT) LOAD * resident CROSS.Colors.PropertyValueID;

left join (RESULT) LOAD * resident CROSS.Make.PropertyValueID;

left join (RESULT) LOAD * resident CROSS.Machinery.PropertyValueID;

drop tables CROSS.Colors.PropertyValueID, CROSS.Make.PropertyValueID, CROSS.Machinery.PropertyValueID;

Transforming rows into columns

ali_hijazi — Fri, 20 Jan 2012 14:51:40 GMT

thank you for your support but I have one thing

the number of rows is dynamic and the table Result of mine has more than 9 milliions records

I need a generic way to apply left join on result

since colors.propertyvalueid and the rest are not static

Transforming rows into columns

Jason_Michaelides — Fri, 20 Jan 2012 15:04:22 GMT

See Alex's solution here:

http://community.qlik.com/message/78337#78337

Hope this helps,

Jason