Pivot over Dimensions?

  • 19 November 2015
  • 6 replies

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?



6 replies

Userlevel 6

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.



Userlevel 6

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.



Userlevel 5

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.