topic Re: How to Transform Rows to Columns and Columns to Rows, in QlikView

How to Transform Rows to Columns and Columns to Rows,

amithmurali — Mon, 08 Jun 2015 12:28:55 GMT

Hi guys, i am having a problem here. i have an excel sheet. i need to transform some rows into columns and the columns into rows. how to do it.

I have the format like this,

Facility	Date	Categories	Doctors	Nurses	Paramedics	Admin & Support
ortho	Jan-15	Transfer Out	10	8	2	42
ortho	Jan-15	Transfer In	3	4	6	8
ortho	Jan-15	New Joinees	0	3	1	1
Cardio	Feb-15	Transfer Out	0	0	0	0
Cardio	Feb-15	Transfer In	3	2	1	6
Cardio	Feb-15	New Joinees	13	10	3	48

i want to load the Excel sheet like this

Facility	Date	Type	Transfer Out	Transfer In	New Joinees
ortho	Jan-15	Doctors	10	3	0
ortho	Jan-15	Nurses	8	4	3
ortho	Jan-15	Paramedic	8	6	1
ortho	Jan-15	Admin & Support	42	8	1
Cardio	Feb-15	Doctors	0	3	13
Cardio	Feb-15	Nurses	0	2	10

QlikView‌App Development‌Creating Analytics‌

Re: How to Transform Rows to Columns and Columns to Rows,

Anonymous — Mon, 08 Jun 2015 12:38:25 GMT

when you import the excel file use the transformation step and use Cross table

Should work

Re: How to Transform Rows to Columns and Columns to Rows,

buzzy996 — Mon, 08 Jun 2015 12:39:08 GMT

u can simply load ur data first,

then use the pivot table and manually u can move rows as column or columns as rows on pivot table

else in ur edit script,u can use crosstable() for this see this thread

Manipulating Data / Cross Table

Re: How to Transform Rows to Columns and Columns to Rows,

Gabriel — Mon, 08 Jun 2015 12:41:10 GMT

Hi,

CrossTable is your friend in this case

Re: How to Transform Rows to Columns and Columns to Rows,

Gysbert_Wassenaar — Mon, 08 Jun 2015 12:44:25 GMT

You can use the crosstable function to transform the data.

CrossTable(Type, Value, 3)

LOAD

Facility,

Date,

Categories,

Doctors,

Nurses,

Paramedics,

[Admin & Support]

FROM ...excel_file....

Then create a pivot table object with Facility, Date, Type and Categories as dimensions. Drag the Categories dimension to the right above the expressions to pivot it to horizontal layout.