19 Replies Latest reply: May 30, 2012 4:25 PM by Michael Stevens RSS

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

Michael Stevens

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

    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.Image 53390.png

    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?
      Michael Stevens

      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

        Hi Mike,

         

        Here is the script

         

        LOAD Main,
            
        Main#

        FROM
        [c:\Thread53390.xlsx]
        (
        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
        [C:\Thread53390.xlsx]
        (
        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?
          Michael Stevens

          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

            Hi Mike

             

            I atttached that to my posting last night.

             

            Joe

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

              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

                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
                [C:\Thread53390.xlsx]
                (
                ooxml, embedded labels, table is RawData);
                LOAD Main#,
                    
                SubFROM
                [C:\Thread53390.xlsx]
                (
                ooxml, embedded labels, table is Sub1);

                LOAD Main,
                    
                Main#FROM
                [C:\Thread53390.xlsx]
                (
                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?
                  Michael Stevens

                  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.