Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Turning Column values into rows

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!

8 Replies
cspencer3
Creator II
Creator II

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.

jafari_ervin
Creator III
Creator III

Hi

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

maxgro
MVP
MVP

here

The Crosstable Load

script should be

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

Not applicable
Author

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.

cspencer3
Creator II
Creator II

Any odds you can post a sample of the data?

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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?