Transpose columns to rows

[Chloe19]

I have the below 

I have the below problem 

I am uploading an excel file which looks like the below image.

I am trying to transpose it so the contents of the questions (columns) is in the rows. 

That being said I would still need it to be filterable using the Region/Districts.

Any advice/guidance is appreciated

[brunobertels]

Hi 

You need to crosstable your table 

see here :

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross-... 

You may find more ressources here or in google with keyword crosstable load , pivot data 

Hope it helps

[KJM]

Hi Chloe,

You can achieve this by using Crosstable in the Data Editor.

I've created an app with your data to show you how to.  Look at the data editor where I've first created your original data as an inline (you'll just load your excel as normal) and then I've used the Crosstable function to transpose the data.  If you then look at the sheet I've created a table where you can count the results per question and filter it by region etc.  

If this solves your problem please mark it so 🙂

K.

[Chloe19]

Thanks KJM

Really appreciate the lengths you went to but I am unable to open qvf file on my work machine.

Do you mind pasting the solution here?

Thanks,

Chloe

[KJM · Accepted solution]

No problem

The script I used was

Table:
Load * Inline
[
	Review_Date, Region, District, "1.1 Was it clean?", "1.2 Was it smelly?", "1.3 Was it closed?"
    24/07/2024, North, Easterm, Inconsistent, Inconsistent, Inconsistent
    25/07/2024, South, Western, Consistent, "Below Expectations", Inconsistent
];

Table_Crosstable:
Crosstable(Question, Result, 3)
Load * Resident [Table];

Drop Table Table;

So then in the sheet itself I used, for example, Count({<Result={'Consistent'}>} Result) as the expression for a measure in a table where the other column was Question.  See image below:

Hopefully that solves it for you.  Let me know,

K.

[Chloe19]

Can't thank you enough!

I really did spend the entire day reading about how to do it but I failed at every attempt.

You are a gigantic life saver and you taught me something new.

Thank you @KJM !!!

Can I ask one more thing, what is the easiest way to limit the pivot table to only show your specific "Questions" for examples if I wanted it to stop at 1.2 and not show 1.3, or down the line if I wanted to show only question between 1.5 to 1.10?

[KJM]

No problem, its only easy when you know!

Ah we had a similar query yesterday.  Essentially we found that numbering our audit questions 1.1, 1.2 etc caused difficulties when there were more than 9 questions in a section.  When we tried to filter by questions between 1.5 and 1.10 as you say it was reading 1.10 as the same as 1.1.  We ended up changing the format of the question numbering to have hypens instead (1-1, 1-2 etc) and created a reference table in the data model to link to the section, question number, question summary, full question etc.  Sorry that's probably not the best news for you but associating to a reference table can future proof it when the audit changes.