Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jleefjcapital
Contributor

Transposing columns into rows

I'm using a data base where each year is the column for the data.  I would like to create a new column called "Year" and transpose the data accordingly.

Here is how the data are currently stored:

This is how I'd like to display the data :

Thank you.

8 Replies
MVP
MVP

Re: Transposing columns into rows

QlikView has a wizard to do this, you can use QlikView to come up with the script and then use the same script in Qlik Sense (you will just have to change the paths)

jleefjcapital
Contributor

Re: Transposing columns into rows

I'll try that.  But once I transpose, I'll get the following table (below). 

I'm looking to create the following fields from this table.

(1)  United States, IndustryId = 1 for all years   as GDP total_US

(2)  United States, IndustryId=2 for all years     as  Private Industries_US

(3)  United States, IndustryId=3 for all years     as Agriculture, forestry, fishing and hunting_US

(4)  Abilene, Tx, IndustryId = 1 for all years       as GDP total_Abilene

(5)  Abilene, Tx, IndustryId=2 for all years         as  Private Industries_Abilene

(6) Abilene, Tx, IndustryId=3 for all years          as Agriculture, forestry, fishing and hunting_Abilene

Thanks!

MVP
MVP

Re: Transposing columns into rows

If you can share your excel file, we can try to play around with it

jleefjcapital
Contributor

Re: Transposing columns into rows

Thanks, Sunny.  Here's the excel file. 

MVP
MVP

Re: Transposing columns into rows

I will try this out at home and get back to you.

Best,

Sunny

jleefjcapital
Contributor

Re: Transposing columns into rows

Thanks!

swetafuria02
New Contributor II

Re: Transposing columns into rows

Hi Lee,

You will have to create a unique key by concatenation of industry ID and Geo name in the required format that you have mentioned above. Then load that field from your load script and use the same in the table.

Similarly for any other data display if u have duplicates you can just create a unique key by concatenation and work on that data.

Hope this helps.

Sweta

jleefjcapital
Contributor

Re: Transposing columns into rows

Thank you.