topic Pivoting name-value pairs into columns in QlikView

Pivoting name-value pairs into columns

Thu, 11 Mar 2010 00:06:07 GMT

Hi guys. I've got a data set with 3 columns: ID, Field, and Value. I want to pivot the table so the columns are the values of Field with the corresponding Values in rows. Does this make sense? Let me know if you have any ideas. Thanks!

Eric

Pivoting name-value pairs into columns

amars — Thu, 11 Mar 2010 05:57:10 GMT

See if this helps

http://community.qlik.com/forums/t/18077.aspx

What u need to do is select a column by clicking on the header of the column & try dragging it a blue arrow will appear & try putting it horizontally . so the columns will get converted to rows.

Pivoting name-value pairs into columns

Thu, 11 Mar 2010 23:43:46 GMT

No I'm not trying to do this in a chart. I want to do it in the script so I actually have columns I can pivot around. So for instance, if my table looks like this:

[ID | Field | Value]

1 | Name | Eric

1 | State | California

1 | Job | Programmer

2 | Name | Mark

2 | State | California

2 | Job | Manager

I want the resulting table to look like this:

[ID | Name | State | Job]

1 | Eric | California | Programmer

2 | Mark | California | Manager

Does this make sense?

Pivoting name-value pairs into columns

blaise — Thu, 11 Mar 2010 23:52:20 GMT

have a look at the crosstable function.

Pivoting name-value pairs into columns

Fri, 12 Mar 2010 01:29:42 GMT

I tried using crosstable, but it doesn't seem to do the right thing. My data is in the form of name value pairs, where one column contains what should be the field name, and the next column contains the value. The crosstable function would do the opposite of what I want, basically if I had a table with regular columns, the crosstable function would turn it into the format that I have now. Is there another way to transform name-value pairs?

Pivoting name-value pairs into columns

Fri, 12 Mar 2010 02:09:58 GMT

Here is a quick and dirty solution.

This could probably be done in a loop as well to automate it.

Maybe someone knows of a function that can do this automatically.


RAW:

LOAD * INLINE [
 Key, Field, Value
 1, Name, Eric
 1, State, California
 1, Job, Programmer
 2, Name, Pete
 2, State, California
 2, Job, Manager
];


COOKED:

load Key, Value as Name resident RAW where Field = 'Name';
join
load Key, Value as State resident RAW where Field = 'State';
join
load Key, Value as Job resident RAW where Field = 'Job';

drop table RAW;

Re. :Re: Pivoting name-value pairs into columns

fredericmante — Fri, 12 Mar 2010 08:28:20 GMT

have you test a 'Generic Load'

regards