topic Re: Turning Column values into rows in QlikView

Turning Column values into rows

Tue, 21 Jul 2015 18:06:41 GMT

Hi,

I am having some real difficulty coming up with a solution to my problem.

I have an excel file that is the results from a survey of a ton of locations.

The Headers look like this: Location , Question 1, Question 2, Question 3

with the values like this: Location 1, answer 1, answer 2, answer 3

Location 2, etc, etc, etc

I want to switch it so that it instead looks like this:

Headers: Location     Question        Answer
              Location1     Question1      Answer1
              Location1     Question2     Answer2
              Location1     Question3     Answer3

No matter what I try to think of, nothing comes to mind.

I tried Inline but I cannot insert the answers into it from my table.

I dont know if this is possible but if it is, help is greatly appreciated!

Thanks!

Re: Turning Column values into rows

cspencer3 — Tue, 21 Jul 2015 18:40:35 GMT

Can you solve this by using the 'Crosstable' functionality when using the Table Files load from your excel file? It seems like this may be an option to reformat your file when loading it.

Re: Turning Column values into rows

jafari_ervin — Tue, 21 Jul 2015 18:43:19 GMT

You can drag the question column to the header row in pivot table then you see the desirable result.

Re: Turning Column values into rows

maxgro — Tue, 21 Jul 2015 18:46:41 GMT

here

The Crosstable Load‌

script should be

crosstable (Question, Answer) load Location, Question1, Question2, ...... from ......

Re: Turning Column values into rows

Tue, 21 Jul 2015 18:47:02 GMT

The problem with this is there a multiple locations. The result is something like this:

Q1: A1

Q2: A2

             Q3: A3
     Loc1
     Loc2
     Loc3

So the data isn't correct.

Re: Turning Column values into rows

cspencer3 — Tue, 21 Jul 2015 18:48:50 GMT

Any odds you can post a sample of the data?

Re: Turning Column values into rows

Tue, 21 Jul 2015 19:03:19 GMT

I wish I could but it is about my work and contains confidential information.

I can make a mock up of some of the data if that would help

Re: Turning Column values into rows

Tue, 21 Jul 2015 19:06:05 GMT

I will try this.

The rough part is there are about 40 questions so it will take some time but might be worth it.

Will respond after trying a few questions.

Thanks

Re: Turning Column values into rows

Tue, 21 Jul 2015 19:15:34 GMT

damn you're good.

Thanks a ton.

Follow up question:

can you do a cross table with more than 2 values?

Some of the questions have follow up questions with them such as

q: 'do you have a fence?' a: 'yes'
Q: 'how high is it?' a: '6 ft'

might it be good to make a second cross table of just follow up answers then concat/join them?