6 Replies Latest reply on Jul 22, 2017 2:41 PM by Jonathan Drummey

    Pivoted Data: Editing Aliases and Filtering

    Mark Schar

      Hi all - I'm working with a 100 question- 6,000 respondent survey set.  I've pivoted/reshaped the data so I have two "columns" - Variable (Dimension) and Data (Measure).  I can filter the "Variable" to sort out the questions I'm interested in including in a worksheet. However, I encounter two problems which are vexing:


      1. Editing Aliases - When I edit an alias, changing 0 to "Female" it changes all 0's in the database to Female, however in other variables 0 may equal "Definitely Will Not," for example.  In this data format, how do I limit the editing of aliases to a certain set of variables and not all variables?
      2. Filtering: I'm having a similar problem with filtering.  Before pivoting, I could drag a Dimension to the Filter Filter window and create a filter for the data in a worksheet.  No that is not possible.  So how do I create a filter for just a set of variables defined in a worksheet when the data has been pivoted.


      Thanks for your help with this.  Mark

        • 1. Re: Pivoted Data: Editing Aliases and Filtering
          Susan Baier

          Hi Mark,

          If you're interested in having different aliases for a given dimension, you could duplicate that dimension and assign the new aliases to one of them. So you could duplicate your "Variable" dimension and apply a second set of aliases to the duplicate.


          One of the things I use often are Likert scale questions with a common range (1-10, for example) but in which the range is used for different scales -- agreement/disagreement, level of interest, etc. When showing the data I use reference lines that are labeled with the appropriate scale -- so I'll add a reference line for a constant, with an appropriate custom label ("1-Disagree completely") that will show up on my axis in the appropriate place. This way I don't have to change any aliases for 1-10.


          I'm not 100% sure if this will solve your problem, but you can always duplicate a field in your data set and reshape one of them and leave the other one alone (tools like Alteryx allow you to select a field as both a key field and a pivot field prior to reshaping -- not sure if R does that, but you can always just create a duplicate field in your data set before reshaping). This would allow you to filter by responses to a particular field, even if you also need that field to be part of your reshaped data.


          Hope that helps.



          • 2. Re: Pivoted Data: Editing Aliases and Filtering
            Mark Schar

            Thanks Susan!  You're response has me thinking and I think I've found a way to solve the problem.  It will take more than reference lines, although that's a slick suggestion.  In the data, 0 = "Female and 0" = "definitely will not", for example, so some recoding will be required.  My solution is to recode the data to give each response a different value. So 0 = Female, but 0+100 or 100 can equal "definitely will not" (etc.) and I can still calculate averages and other statisictics. And you suggestion to duplicate some variables (and not pivot them to use) as filters is a breakthrough thought for me.  Once again, thanks!

            • 3. Re: Pivoted Data: Editing Aliases and Filtering
              Mohammad Aryaie Fakher

              Hey Mark,


              Have you tried creating a new calculated field with CASE?


              It can be something like:

              IF CONTAINS([Answer],"keyword")=TRUE

                   THEN CASE [Answer]

                   When "0" THEN "Female"


              ELSE IF CONTAINS([Answer],"keyword2")=TRUE

                   THEN CASE [Answer]

                   When "0" THEN "Definitely will not"



              1 of 1 people found this helpful
              • 4. Re: Pivoted Data: Editing Aliases and Filtering
                Susan Baier

                Hi Mark! Just saw your note. Glad my approach was helpful.



                • 5. Re: Pivoted Data: Editing Aliases and Filtering
                  K R

                  Hi Susan,


                  I am referring to your reply for above issue. I have a similar situation. I need to pivot all the columns (except ONE) in the Data. Though I need few Key columns to be duplicated and used as Filters to drill down the data.


                  As you mentioned above, I tried to duplicate the required Key Columns and then pivot the original fields. Unfortunately, this did not work.

                  Issue: The duplicated columns have calculation errors and are not useful.


                  How can I overcome this. Any help will be highly appreciated.


                  Thank you.

                  • 6. Re: Pivoted Data: Editing Aliases and Filtering
                    Jonathan Drummey

                    Hi K R,


                    There are a couple of ways to get this. In both cases I'm assuming that the one column you aren't pivoting is  something like a unique response ID.


                    Method 1: self-join. Here's the steps:


                    1) Pivot your raw data.

                    2) Add a join to the raw data on the response ID.

                    3) Hide any columns you don't need from the raw data.


                    Method 2: level of detail expressions. This method uses level of detail expressions to aggregate across the pivoted rows to create new dimension(s) you can use for filters, as dimensions in views, etc.  You'd create a calculation for each dimension you want to create.


                    Here's the steps:


                    1) Pivot your raw data.

                    2) Create a calculated field for each dimension. For example let's assume there's are fields called Gender and Age that get pivoted, then there would be two calculated fields:



                    {FIXED [Response ID] : MAX(IF [Pivot field names] = 'Gender' THEN [Pivot field values] END)}



                    {FIXED [Response ID] : MAX(IF [Pivot field names] = 'Age' THEN [Pivot field values] END)}


                    I'd tend to go with method #1 since it's a) entirely point & click and b) will almost certainly have higher performance.



                    1 of 1 people found this helpful