topic Re: Change Rows to Columns in QlikView

Change Rows to Columns

qlikwiz123 — Sat, 16 Nov 2024 18:26:49 GMT

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

Re: Change Rows to Columns

Saravanan_Desingh — Thu, 25 Jun 2020 02:24:27 GMT

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;

Re: Change Rows to Columns

Saravanan_Desingh — Thu, 25 Jun 2020 02:25:21 GMT

Output.

Re: Change Rows to Columns

Vegar — Thu, 25 Jun 2020 06:50:22 GMT

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

Re: Change Rows to Columns

Brett_Bleess — Thu, 09 Jul 2020 12:33:31 GMT

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