Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator II
Creator II

Change Rows to Columns

Hi,

I have an Excel with Data as below

FunctionCountryAreaWhiteBlackHispanicAsianOthersSource
TechUSChicago62%9%9%17%3%Ext

 

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

 

 

FunctionCountryAreaEthnicityPercent Value
TechUSChicagoWhite62%
TechUSChicagoBlack9%
TechUSChicagoHispanic9%
TechUSChicagoAsian17%
TechUSChicagoOthers3%

 

In short, I need to convert rows into Columns.

 

Thank you

 

Labels (2)
4 Replies
saran7de
Master
Master

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;
saran7de
Master
Master

Output.

commQV72.PNG

Vegar
Partner
Partner

As @saran7de  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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.