2 Replies Latest reply on May 16, 2013 12:23 AM by taina.larjasmaa

    Reshaped Excel data: Q and A data types in Tableau

    taina.larjasmaa

      Hello,

       

      Official data, I should analyze using Tableau is in Excel files and very often the files consist of more than 255 columns. Hence, I have happily used Tableau data reshaping tool for Excel. 

      Source data (which is reshaped) consists of number type of fields and text type of fields. After reshaping data, number and text fieds are changed to "General".

      ¨When connected to reshaped source data, the Question field shows as a dimesion in Tableau data view and the Answer field as a measure.

      The data type in some answers should be string, because the answers are text like "satisfied" or "no".

      When I create a view in Tableau to get the average of the answers, it works fine for the numric fields, but string fields are left empty.

      If I change the data type of Answer field in Tableau, it affects the numeric responses and still does not return correct text in the view.

       

      I would really appreciate, if you gurus could help me in this!

       

      I attach my test files (Excel and Tableau) to clarify my presumably confusing text above.

       

      BR,

       

      Taina

        • 1. Re: Reshaped Excel data: Q and A data types in Tableau
          Jim Wahl

          Hi Taina,

           

          On way to do this is to specify the datatype in Excel---make the column a text column. Then in Tableau, you can create a calculated field to parse this; for example:

          CASE [Answer]

          WHEN "satisfied" THEN 10

          WHEN "neutral" THEN 5

          WHEN "dissatisfied" THEN 1

          WHEN "yes" THEN 10

          WHEN "maybe" THEN 5

          WHEN "no" THEN 1

          ELSE INT([Answer])

          END

           

          If you do not want to convert the text values (and instead want to show % of responses in each category), then you'll need to create multiple worksheets and combine them in a dashboard. There are many ways to do this, but I might create three Answer fields, one for each question type (numeric, satisfied, and yes/no/maybe).

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Reshaped Excel data: Q and A data types in Tableau
            taina.larjasmaa

            Hello Jim,

             

            Thanks for your quick and helpful response!

            I will test the solution, you suggested, in "real life" and possibly get back!

             

            Appreciating,

             

            Taina