topic Re: Does anyone know how to create multiple rows in pivot table situations? in QlikView

Does anyone know how to create multiple rows in pivot table situations?

Tue, 15 May 2012 11:39:00 GMT

Hi,

I am relatively new to Qlikview and I have been set a challenge to generate an application around a recent survey conducted by my workplace.

My initial thoughts were to generate a pivot table that would have columns of data such as gender, ages, overall totals and percentages and then within the rows themselves I would build in the variety of questions and their options as detailed in the survey i.e. 'Q1: What did you think of this service?' - 'Option 1: Very good - Option 2: Good', etc..

Nothing is as straight forward as I had first thought as each time I add an additional question beneath existing row it basically adds it in a drill down format i.e. 'Q1: What did you think of this service?' followed by 'Question 2' which is added as a sub category of question 1 where as I want it to be a seperate and new entry within the row fields.

The attached excel spreadsheets hopefully demonstrates clearly what I currently am viewing (tab 1) and what I want to be able to achieve with this (tab 2).

At present, I have adopted a 'Cyclic Group' process to the application where I am able to rotate the questions round in order to obtain the results I need. However this isn't really the ideal outcome I was hoping for since I want to be able to show all of the data in a pivot table format which can than be transported across to Excel for further examination or for distribution.

Any help or advice would be gratefully received.

Thanks and regards, Mike.

Re: Does anyone know how to create multiple rows in pivot table situations?

mazacini — Tue, 15 May 2012 22:24:57 GMT

Hi Mike

I'm not an expert, but as no other assistance seems to be forthcoming, I'll give it a go!

Not sure what your source data looks like, but the result in the attached image can be achieved as long as the data comes in Sheets 1 and 2 in the attached spreadsheet.

Let me know if this is something that might work, and I'll give you some more detail.

(BTW, I know the % is not exactly what you wanted, but I 'm sure that's an easy fix - my head is a bit frazzled right now.)

Rgds

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

Wed, 16 May 2012 11:42:45 GMT

Hi Joe,

I think this is exactly what I have been trying to attempt to achieve. I would be grateful if you could let me know how you went about achieveing this layout. I'm not too worried about the percentages at this stage. More so about getting the look of the matrix grid right which is something that has been perplexing me for some time now.

Any help / advice would be gratefully received.

Thanks and regards, Mike.

Re: Does anyone know how to create multiple rows in pivot table situations?

mazacini — Wed, 16 May 2012 13:02:48 GMT

Hi Mike,

Here is the script

LOAD Main,
Main#

FROM

(ooxml, embedded labels, table is Sheet2);

CrossTable(Party, Count,2)

     LOAD Sub,
     Left(Sub,3) as Main#,
     [The Guild],
     [The University],
     [Third party provider]

FROM

(ooxml, embedded labels, table is Sheet1);

You will need to replace the FROM location to suit your local folder.

Then make a Pivot Chart. Relevant settings are:

Dimensions:

Sub

Party

Expressions:

Label: Count

Definition =Sum(Count)

Presentation

Check "Always Fully Expanded"

Check "Show Partial Sums" for Sub and Party

Check "Subtotals at bottom"

Above excludes % expression.

Let me know if you have any more queries.

Joe

Does anyone know how to create multiple rows in pivot table situations?

Wed, 16 May 2012 13:22:32 GMT

Joe,

Many thanks for this. Would it be possible for you to add the excel spreadsheet (Thread53390.xlsx) from which the script works from so that I can see how all of this works and how I can apply it to one I have here. I should have supplied you with a sample of the data I use in the last message but forgot to do so.

Many thanks, Mike.

Does anyone know how to create multiple rows in pivot table situations?

mazacini — Wed, 16 May 2012 16:09:35 GMT

Hi Mike

I atttached that to my posting last night.

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

Thu, 17 May 2012 10:23:15 GMT

Hi Joe,

I didn't see the two new additional tabs on the excel spreadsheet. Apologies.

The solution you have provided is exactly what we are looking for.

What I should have done, from the start, was to let you see the raw data from which the application is currently linked to. As you can see from the 'RawData' tab the data is laid out in a different way to what you have laid in your 'Sub' tab. All I can say is the data has been downloaded from a SNAP survey software into a csv formatted excel spreadsheet.

As you will no doubt see that the main question is not embedded from within it but that is because we already now that the sub categories are linked to the relevant question, which in this case is related to Q10. Having said that I can work the key questions into the application as per your suggestion in the 'Main' tab.

Is there anyway in which the 'RawData' tab could be made workable within the parameters of your solution. Some of the other questions will have 'Yes / No' - 'Very Good / Good / Average / Poor / Very poor' responses as resulting responses. Some of the questions will contain responses that are the same as the questions themselves as per Q11 columns shown in the 'Raw Data' tab.

Hope this all makes sense.

Thanks and regards, Mike.

Re: Does anyone know how to create multiple rows in pivot table situations?

mazacini — Thu, 17 May 2012 13:52:22 GMT

Hi Mike.

You will need to load the Raw Data in as a crosstable load.

However, my understanding (and I stand to be corrected!) is that you need a qualifier field in a crosstable load. I have assumed to insert a column for that purpose, named "Respondent". In fact I can leave that blank, as I will only use it to facilitate the load.

I am ignoring the Sub tab, as we are now going to get the model to provide these figures.

I have created a new Sub1 tab which links the Sub Question to the Main Question. I should be able to create this in the script (as I did previosly), but it is not working so I have come up with this workaround.

New XL file attached.

If you proceed, you wll need manually maintain the Main and Sub1 tabs on an ongoing basis.

Here's the new script:

CrossTable(Sub, Response)LOAD *
FROM

(ooxml, embedded labels, table is RawData);
LOAD Main#,
SubFROM

(ooxml, embedded labels, table is Sub1);

LOAD Main,
Main#FROM

(ooxml, embedded labels, table is Main);

In your chart, Dimensions are now Sub and Response;

Your Expression is

Count(Response)

Display the Main Questions by Select Field > Mian

Rgds

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

Fri, 18 May 2012 14:05:58 GMT

Hi Joe,

Many thanks for the last post. I think we are almost there.

I have a few additional queries which I hope you will be able to assist me in answering.

I now want to be able to resolve the problems I am experiencing with regards to calculating totals for each of the question sets broken down by a variety of parameters (gender, ages, locations, etc.) from within this matrix. As you will see from the attached spreadsheet what is currently happening is the figures shown in the matrix is for the overall total for the question(s) themselves and not necessarily the totals for the breakdowns (gender, ages, location, etc.). I have tried to work around this by applying ‘Count’ and ‘Sum’ functions to the data but as yet none appear to work the way I want it to.

Do you know of any other way I can overcome this problem?

Also, as you can see from the spreadsheet I have applied questions in row and in columns so for instance I want to show Genders, Ages, Locations in columns and then show actual questions in the rows. I think what I am trying to achieve here is a demographic overview of the figures. I can achieve this with one set (Gender) however if I apply another set to the matrix it is applied as a next level down from Gender and not necessarily alongside it which is what I want to be able to achieve.

Last but not least, is there any way I can script an option to only view data where it has been ‘selected’ and omit data where it has been marked as “Not Selected” or “Not Declared” from within the matrix. I have tried to add a simple list box to the application and each time I select for instance ‘Males’ and ‘Females’ only the change on the list box doesn’t affect the change from within the matrix.

Apologies for bombarding you with even more questions.

Thanks and regards, Mike.

Re: Does anyone know how to create multiple rows in pivot table situations?

mazacini — Mon, 21 May 2012 14:27:40 GMT

Hi Mike

I'll try and take a look this evening.

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

mazacini — Mon, 21 May 2012 15:07:08 GMT

Hi Mike

There are a number of issues here. I'm not sure of I can get my head around each, but let me clarify the nature of the data firstly.

Firstky, can you confirm how you capture the gender and age data for each respondent?

Simplest would be if there were 2 additional columns in the RawData tab, one for gender, and one for Age group

You then have 2 dimensions that you can use to analyse.

Joe

Does anyone know how to create multiple rows in pivot table situations?

Mon, 21 May 2012 15:32:07 GMT

Hi Joe,

I can confirm that 'Gender' and 'Age' (as do all of the data) have columns within the original dataset where entries are made within the rows as per the attachment on 17th May 2012 which gives you an idea of how the data looks.

Regards, Mike.

Does anyone know how to create multiple rows in pivot table situations?

mazacini — Mon, 21 May 2012 21:48:03 GMT

OK. You will need to amend the crosstable load to pull in this information.

In the RawDat, I insert 2 new columns (Age and Gender) to the right of the Respondent Column.

My script changes to

CrossTable(Sub, Response,3)

This creates and additional 2 quaifier fields (Age and Gender). Age and Gender are now available as Dimensions in the modle, and can be used to extend the pivot for analysing repsonses/respondents.

Re: Does anyone know how to create multiple rows in pivot table situations?

Wed, 23 May 2012 11:17:11 GMT

Hi Joe,

Hope this finds you well.

I just wanted to say many thanks for your solution to my previous query. It has resolved a lot of issues from our point of view and has created all kinds of possibilities with this new matrix.

What I am now attempting to achieve with the new layout is calculating percentages, which I thought would be a fairly straightforward thing to achieve is proving somewhat elusive. Perhaps I have overcomplicated what I am attempting to achieve.

What I want to be able to achieve is columns where the total equate to 100% and the cells within each column to be calculated as a percentage of the overall total for the selected criteria.

The attached spreadsheet shows the ‘Current outcome’ which is what I am experiencing at present where the calculations are determining percentages as an overall total of each row.

I think the problem lies in that I have two tiers working within the columns which are making the calculations less straightforward as I would like it to be.

I know there is a simple way of overcoming this but at this stage all my attempts thus far to get it to work is proving fruitless.

The calculation I have adopted (detailed in the spreadsheet) will work if only one of the two tiers appear in the columns but not when there are more than one tier from within the columns.

Any help would be gratefully appreciated.

Thanks and regards, Mike.

Does anyone know how to create multiple rows in pivot table situations?

mazacini — Fri, 25 May 2012 10:52:03 GMT

Hi Mike

Haven't abandoned you.

Just haven't been on the site for a few days.

Try this:

=count(Response)/count(TOTAL <Gender>Response)

Rgds

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

Tue, 29 May 2012 09:48:24 GMT

Hi Joe,

I have just got around to trying this out and I am not getting the desired result.

I copied your suggested expression as per:

=count(Response)/count(TOTAL <Q1 Gender> Response)

I notice that the <Q1 Gender> Response)remained black. I have attempted a variety of different options such as inserting brackets at various points in the expression but to no avail. The percentage column in the matrix returns dashes where the data should be. Any suggestions?

Thanks and regards, Mike.

Does anyone know how to create multiple rows in pivot table situations?

mazacini — Tue, 29 May 2012 11:40:24 GMT

Hi Mike

I may have mislead you,as I named your Tier Two dimension as Gender.

If you replace the word Gender with the name of the Tier Two dimension, does it work?

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

Tue, 29 May 2012 11:49:24 GMT

Joe,

With a bit of twiddling and fiddling I came up with this solution:

=count(Response)/count(TOTAL) <[Q1 Gender]> Response)

...and it works!!

Thanks and regards, Mike.

Does anyone know how to create multiple rows in pivot table situations?

mazacini — Tue, 29 May 2012 13:31:13 GMT

Hi Mike

Well done on that.

If any of the above answers proved correct ot hepful, you might mark them accordingly?

Rgds

Joe

Re: Does anyone know how to create multiple rows in pivot table situations?

Wed, 30 May 2012 20:25:42 GMT

Hi Joe,

On presenting the new matrix to my line manager a query was raised as to whether the percentage calculation could be reworked to look something along the lines of the attached spreadsheet.

The green cells is what I want to be able to achieve. It is basically a percentage of the total sum of the 'Sub' questions for each of the 'Responses' ((Selected or Not Selected / Q7a or Q7b or Q7c and so forth) or (1063 / 1320 for Q7a Not Selected and 257 / 1320 for Q7a Selected and so forth)).

Is there an easy way of achieving this, as this has me somewhat baffled at present in attempting to translate this simple calculation into script format.

Hopefully this will be last thing I need to do to complete this matrix.

Many thanks and regards, Mike.