How to pivot data table without access to dataset?

Question & Answer
, ,
1

I have a data table that contains answers to questions from a form. I am trying to pivot the data in an Analysis to show the questions as columns and the corresponding answers in rows. I do not have access to modify the dataset so I need to use calculated fields in the analysis.
Here is rough simplification of my source data:

form_id question answer
12345 Location San Diego
12345 Priority 2
12345 Project Title Project 1
12345 Region West
54321 Location Devens
54321 Priority 2
54321 Project Title Project 2
54321 Region Central
abcxyz Location New Brunswick
abcxyz Priority 4
abcxyz Project Title Project 3
abcxyz Region East

This is roughly what I want to achieve:

Project Title Location Priority Region
Project 1 San Diego 2 West
Project 2 Devens 2 Central
Project 3 New Brunswick 4 East

The form_id is the key column to match the answers between the different questions. In DAX I could write a custom formula to lookup the corresponding values but I have not found any equivalent lookup in QS. The closest I have gotten is by using firstValue in a calculated field like:

firstValue(answer,[{form_id} ASC],[{form_id},question])

If I put this in a matrix with the corresponding values I get this:

image
image796×226 14.2 KB

This is close but it beaks each unique value into a separate column. There should only be one answer to each question per form_id. Any thoughts?

2

You can transform your form data into a pivoted table in Amazon QuickSight by creating specific calculated fields for each question type and using conditional functions. This approach allows you to extract specific answers for each question type per form_id without modifying the underlying dataset [.

Creating Calculated Fields for Each Question Type

Step 1: Create Individual Calculated Fields
For each question type, create a separate calculated field using the ifelse() function:

For Project Title:

ifelse(
    {question} = 'Project Title',
    {answer},
    NULL
)

For Location:

ifelse(
    {question} = 'Location',
    {answer},
    NULL
)

For Priority:

ifelse(
    {question} = 'Priority',
    {answer},
    NULL
)

For Region:

ifelse(
    {question} = 'Region',
    {answer},
    NULL
)

Step 2: Use maxOver() Aggregation
To consolidate these values by form_id, modify each calculated field to use the maxOver() function [3]:

Project Title (consolidated):

maxOver(
    ifelse({question} = 'Project Title', {answer}, NULL),
    [{form_id}]
)

Location (consolidated):

maxOver(
    ifelse({question} = 'Location', {answer}, NULL),
    [{form_id}]
)

Priority (consolidated):

maxOver(
    ifelse({question} = 'Priority', {answer}, NULL),
    [{form_id}]
)

Region (consolidated):

maxOver(
    ifelse({question} = 'Region', {answer}, NULL),
    [{form_id}]
)

Step 3: Create Your Visual

  1. Create a table visual
  2. Add form_id to the “Group by” field well
  3. Add your four consolidated calculated fields to the “Values” field well
  4. (Optional) Remove form_id from the display if not needed

This approach will give you one row per form_id with all question answers as columns [4].

Alternative Approach: Using Level-Aware Aggregations

If your dataset is large, you can use level-aware aggregations for better performance [5]:

max(
    ifelse({question} = 'Project Title', {answer}, NULL)
) OVER ({form_id})

Handling Duplicate Values

If you encounter duplicate values for the same question and form_id, you can use additional logic:

maxOver(
    ifelse({question} = 'Project Title', {answer}, NULL),
    [{form_id}, {some_timestamp_field}]
)

This will take the latest answer based on the timestamp

3

Hi @JoeADSK

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!