topic Converting rows to column using Pivot table in QlikView

Converting rows to column using Pivot table

Wed, 27 Apr 2011 08:15:01 GMT

Hi Everyone,

I have a table which has two columns like NAMES and DEPARTMENT. Now, I want to show a table in which rows of DEPARTMENT should come as columns.

For eg:

LOAD * INLINE [
NAMES, DEPARTMENT
A, 101
A, 102
A, 103
A, 104
B, 101
B, 103
V, 104
];

So the output table would be:

NAMES 101 102 103 104
A Y Y Y Y
B Y N Y N
V N N N Y

The values Y and N are not there in the loaded table. I want to introduce them. I think Pivot table should be use to represent data in this manner, but I am not getting the way to do this. Please give me steps to do this. Any help would be greatly appreciated. It will be helpful for me if you provide any link which has such information.

Regards

Converting rows to column using Pivot table

pat_agen — Wed, 27 Apr 2011 08:31:04 GMT

hi QlikNiks,

I would start by adding a field to your input just for ease of use. Calling the column Indic for example with a value of "1". Your inline load will then look like this:

LOAD * INLINE [
NAMES, DEPARTMENT, Indic
A, 101, 1
A, 102, 1
A, 103, 1
A, 104, 1
B, 101, 1
B, 103, 1
V, 104, 1
];

This create a "fact" for your dimensions Name and Department. It wont eat up lots of space but will make your life easier.

Now your pivot table can be created as a chart, option pivot table. name and department are dimensions and the expression is as follows:

if(Indic>0,'Y','N')

attached is a qvw with thechart

Converting rows to column using Pivot table

Wed, 27 Apr 2011 08:58:11 GMT

Thanks PatAgen,

As I am learning QV on personal edition, I was unable to open QVW file which you have attached. Although, the solution given worked perfectly fine for me.

Best Regards