Question

Pivot over Dimensions?

  • 19 November 2015
  • 6 replies
  • 1210 views

Userlevel 2

Hello Looker & Community,


Part of my Web app collects survey data. Prior to using looker, my users were accustomed to pulling survey data in a particular format, which they would distribute to our customers.


Ideally, my customers would like to see survey data in a format where each line is a form submission as shown in the second picture below. Unfortunately, since the survey answers field is a string, rather than some sort of numeric, I can’t pivot over questions.


Is there any way to get this pivot with dimensions acting as measures for this to work in looker without monkeying around with a complex postgres query?


Thanks!!


Andrew



6 replies

Userlevel 6
Badge

Its ugly, but make answer a field of type list (a measure), then you can pivot out question and the answer will be in the column under the measure.


- dimension: answer

- measure: answer2
type: list
list_field: answer

You should then be able to pivot list out. Let me know how this works out for you.

Userlevel 2

Works like a dream. Thanks for the quick reply!

Userlevel 2

After playing with this a bit, I’ve run into a problem. The query is pulling the right data, but something is happening to the records within looker. When I run my query with a different tool, I do not run into this issue:


After I force the “answer” dimension into a measure and pivot it (issue does not happen when not pivoted), the individual question records are randomly prepended with a comma (","). When I export these files, the comma becomes “|Record|”.


Here’s an updated Example:


Let me know if this is not the right area to ask this question.


Best,


Andrew

Userlevel 6
Badge

Try adding a ‘A is not empty filter’. It looks you have some empty answers in your data set.


Sounds like you’ve discovered a bug exporting lists in CSVs. Filing it now.

Userlevel 2

Any update on the status of this request? I know it’s a bit of an odd one, but I’m getting the random prepended commas and “|RECORD|” in my dataset.


Thanks,


AK

Userlevel 5
Badge

Hey @Andrew_Kraemer, this is on our product team’s radar, but I’m not sure if there are any near-term alternatives, other than what @lloydtabb mentioned above.

Reply