topic Re: Transposing columns into rows in App Development

Transposing columns into rows

jleefjcapital — Fri, 02 Dec 2016 20:01:47 GMT

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.

Re: Transposing columns into rows

sunny_talwar — Fri, 02 Dec 2016 20:05:39 GMT

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)

Re: Transposing columns into rows

jleefjcapital — Fri, 02 Dec 2016 20:17:01 GMT

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!

Re: Transposing columns into rows

sunny_talwar — Fri, 02 Dec 2016 20:23:46 GMT

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

Re: Transposing columns into rows

jleefjcapital — Fri, 02 Dec 2016 20:25:39 GMT

Thanks, Sunny. Here's the excel file.

Re: Transposing columns into rows

sunny_talwar — Fri, 02 Dec 2016 20:40:13 GMT

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

Best,

Sunny

Re: Transposing columns into rows

jleefjcapital — Fri, 02 Dec 2016 20:41:32 GMT

Thanks!

Re: Transposing columns into rows

Anonymous — Sat, 03 Dec 2016 13:42:35 GMT

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

Re: Transposing columns into rows

jleefjcapital — Thu, 08 Dec 2016 23:43:38 GMT

Thank you.