How to Transform Rows to Columns and Columns to Rows,

[amithmurali]

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‌

[Anonymous]

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

Should work

[buzzy996]

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

[Gabriel · Accepted solution]

Hi,

CrossTable is your friend in this case

[Gysbert_Wassenaar]

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.