Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Output.
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
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