Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
amithmurali
Partner
Partner

How to Transform Rows to Columns and Columns to Rows,

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,

     

FacilityDateCategoriesDoctorsNursesParamedicsAdmin & Support

ortho

Jan-15Transfer Out108242
orthoJan-15Transfer In3468
orthoJan-15New Joinees0311

Cardio

Feb-15

Transfer Out0000
CardioFeb-15Transfer In3216
CardioFeb-15New Joinees1310348

i want to load the Excel sheet like this

FacilityDateTypeTransfer OutTransfer InNew Joinees

ortho

Jan-15Doctors1030
orthoJan-15Nurses843
orthoJan-15

Paramedic

861

ortho

Jan-15

Admin & Support4281
CardioFeb-15Doctors0313
CardioFeb-15Nurses0210

QlikViewApp DevelopmentCreating Analytics

1 Solution

Accepted Solutions
Gabriel
Partner
Partner

Hi,

CrossTable is your friend in this case

View solution in original post

4 Replies
jebamalai
Specialist
Specialist

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

Should work

buzzy996
Master II
Master II

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
Partner
Partner

Hi,

CrossTable is your friend in this case

View solution in original post

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.


talk is cheap, supply exceeds demand