2 Replies Latest reply on Jan 23, 2018 9:47 AM by yiren.guo.0

    How to reshape the dataset

    yiren.guo.0

      I have this attached csv file listing 3 types of death, 6 opioid categories as death indicators, and "yes, listed"/"not listed" selection.

       

      In Tableau workbook, we would like to allow single selection in 3 types of death, multiple selections in the 6 opioid categories and yes/no selection, as below. How should I reshape the dataset or create calculated fields in Tableau to allow multiple selections for the 6 opioid categories? When these 6 columns are loaded into Tableau, they become 6 dimensions, and I don't think pivot data from columns to rows in Tableau would give correct numbers.

      The only way I could think of is to prepare the 26 -1=63 groups of data in Excel, e.g., the number of deaths when only opioid40.2 is selected, the number of deaths when opioid 40.2 or 40.3 are selected, the number of deaths when opioid 40.2, 40.3 or 40.4 are selected... I don't think this is smart, because we will have 60+ selections in the drop-down menu for people to select.

      1.png

       

      Please advise! Thanks a lot!

        • 1. Re: How to reshape the dataset
          Jonathan Drummey

          In order to get the interactivity that you want a pivot/transpose is recommended. Usually we can use a Level of Detail (LOD) expression to ensure that the number of deaths wouldn't over-count due to the pivot however that requires some sort of unique record ID (that might be a combination key) and there is none in this data set. So I used Excel to add a Row ID (we don't presently have that capability in Tableau) and then I could build an LOD expression like {INCLUDE [Row ID] : MIN([Number of Death])} and use that as the # of deaths.

           

          Here's a view:

           

          Screen Shot 2018-01-20 at 5.41.07 PM.png

          I prepared a second worksheet based on the original (unpivoted data) that has similar filters in place so I could verify the results. v10.2 workbook is attached, let me know if you have any questions!

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: How to reshape the dataset
            yiren.guo.0

            Thank you so much!!