Structure Data for Analysis

[Kaish]

Hi all, I have a requirement where I have a consolidated file where there are different columns with one column having Group name with all the Group POCs writing their answers for each of the 6 questions provided to them, 6 questions have 6 column, the answers are 'Yes' or 'No' or 'Blanks'. with each question there is a exception category question. In the output I want to get all the questions to be in one column and next to each row there has to be answer which has to be in percentage, how can we achieve this in Alteryx? I am attaching my data and the output attachments, kindly help me out. If the output is complicated in Alteryx we can also use the transformed data to display output in Tableau, Even if the data can be ready for Visualization in Tableau or Power BI, that will also help me out, like the data can be shaped and transformed in Alteryx and then we can use Tableau to populate the results.

Sharing 2 files 1st is the raw file and 2nd is the Output how I want my data to be displayed.

[griffinwelsh]

@Kaish check out the attached solution. This is pretty close to your output png it is just missing the merged cell header. The null exception row is due to a column missing from the input data set.

[Kaish]

Hi @griffinwelsh , your workflow worked perfectly for my data, I had one more query for the same data, if supposed the data in column 3. CSA- Tech enabled is not "Yes" or "No" or "Blanks", it has some different data like "Alteryx" "Tableau" "Power BI" and also if column 4. DD Curve also has some other values like "DD Curves used" "DD Curve with Local" "Not used" instead of "Yes and No and Blanks" and all the Exceptions Category column will have "Business" "Technology" and "Product" these are the random answers instead of "Yes, No, Balnks" Can you help me now ? its just like in the output against the questions we will have these answers with their %. How can we achieve this ? 

Example input : You can put these Values in the original data input file

 3. CSA Tech Enabled       4.DD curve                        Exception Category

   Alteryx                              Not used                             Business

   Alteryx                              DD Curves used                 Product

   Alteryx                              DD Curve with Local           Business

   Tabeau                             DD Curves used                  Business

   Power BI                           Not used                             Technology

   Alteryx                              DD Curve with Local           Technology

Put this data Randomly in the respective column and in all of Exception Category column you can add this, now can you help me out ?

[griffinwelsh]

@Kaish Here is a dynamic version that is value agnostic. Try it with any data set you like and it will output a dynamic number of columns with dynamic column names.

[Kaish]

Hi @griffinwelsh , can you find the new input and output details, this is what I exactly want, can this be done ? kindly let me know.

Please find the input from the first sheet.

[griffinwelsh]

@Kaish This is pretty close to your format. This will leave empty cells if there are not 3 results with non 0 values. Other options are you to maintain an options grouping list for each answer or have one column per possible response. This is probably the cleanest output visually of the 3.