Change Rows to Columns

[qlikwiz123]

Hi,

I have an Excel with Data as below

Function	Country	Area	White	Black	Hispanic	Asian	Others	Source
Tech	US	Chicago	62%	9%	9%	17%	3%	Ext

I need to convert rows into columns and show data as below

Function	Country	Area	Ethnicity	Percent Value
Tech	US	Chicago	White	62%
Tech	US	Chicago	Black	9%
Tech	US	Chicago	Hispanic	9%
Tech	US	Chicago	Asian	17%
Tech	US	Chicago	Others	3%

In short, I need to convert rows into Columns.

Thank you

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    Function, Country, Area, White, Black, Hispanic, Asian, Others, Source
    Tech, US, Chicago, 62%, 9%, 9%, 17%, 3%, Ext
];

tabX:
CrossTable(Ethnicity,[Percent Value],3)
LOAD * Resident tab1;

tab2:
NoConcatenate
LOAD * Resident tabX
Where Ethnicity <> 'Source';

Drop Table tab1, tabX;

[Saravanan_Desingh]

Output.

[Vegar]

As @Saravanan_Desingh  wrote, CrossTable() is the way to go, but you don't need all the preparation steps, you can do the crosstable straight of the excel file. Just make sure that you load your qualifier fields first, notice that I moved Source (the last column in excel) up front before the fields that you want to pivot.

Data:
CrossTable(Ethnicity,[Percent Value],4) //4=four qualifying fields
LOAD 
   Source,   //Qualifying field (1)
   Function, //Qualifying field (2)
   Country,  //Qualifying field (3)
   Area,     //Qualifying field (4)
   White,  
   Black, 
   Hispanic, 
   Asian, 
   Others
FROM Excel.xlsx (ooxml embedded labels);

BR
Vegar

[Brett_Bleess]

You have two very good posts here, we would appreciate it greatly if you would complete the thread by using the Accept as Solution button on the post(s) that helped you with your solution.  If you did something different, you can post what you did and then mark that, and if you still have questions, leave an update comment.

Here is a Design Blog post related to the feature the guys discussed too that may provide further clarification on the use of the feature they discussed.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Regards,
Brett