Can someone please help me in preparing the data as per the desired output. Also need to add one more column which will take the sheet name and put it under location (ex here Asia). Sample file is attached.
Thanks
@Gauri Agarwal Hi Gauri
To transform the Asia file to the crosstab you have prepared as the desired output, there is no direct method to do so either in Tableau Prep or in Tableau Desktop. A small change is still required to your original source data.
This is because Tableau will look for headers for each of the columns and in the case of your source data, there are 2 layers of headers (One layer that contains the date and the questions while the second layer of headers are to represent the individual measures). This will create a bit of confusion and therefore transformation will not be as you would desire.
Therefore, the small changes you will need to make to your source data are as follows.
- Remove Row 1. Tableau cannot read that row as a header that encompasses its underlying group of sub headers.
- Add tags to existing second layer headers so that these tags can later be used to link them to the date and the question. For example, in cell B2, the header of Total invites becomes Q1 Total invites. In this instance Q1 is in reference to the question 2022-04-13: How are you feeling?
Once this is done, then you could use Tableau Prep to do two things.
- Create a duplicate of the same source file that has been updated as instructed above. You will have Asia worksheet and Asia Duplicate worksheet.
- Then create a Union. The purpose of creating a union is so that Tableau Prep will auto generate a column called Table Name. This Table Name column will automatically make references to the name of the worksheets involved in the union. In your example, this will create Asia and Asia Duplicate.
- Then create a clean stage after the union to then remove all records that have a Table Name of Asia Duplicate so that you will have the resulting table that only contains the original data plus a new column that states the name of the worksheet.
- Then create a pivot across all the columns from B through to AA based on your example file.
- Once all these columns have been transformed using the Pivot module in Tableau prep then you can create a calculation to split the headers by stripping off the first 2 characters of our changed headers such as Q1 Total invites. This will provide a new column with only the question references such as Q1 and Q2.
- Then create a calculated field to translate Q1 and Q2 into strings such as 2022-04-13: How are you feeling?
- Then you can further split this new column to extract the Date as well as the Question itself.
- The rest of the procedure involves simply performing cohort analysis either from Tableau prep or by extracting the flow output from Tableau prep and using the output as a source file for Tableau desktop and performing the analysis within Tableau desktop.
Then you should be able to create the desired output from Tableau Desktop.
I hope this helps.
