4 Replies Latest reply on Sep 21, 2015 9:15 AM by Sonu Sharma

    How to merge two dimentions into one dimention

    .Eli Goldfarb

      Hello,

      We have a DB of sales opportunity. each record has Status and Stage dimensions.

      We Would like to be able to merge them in to one dimension.

      see attached excel

        • 1. Re: How to merge two dimentions into one dimention
          Jonathan Drummey

          Hi,

           

          Here's one way to do this, via Custom SQL using a UNION query. I took the original query generated by Tableau and duplicated it, adding two columns: One called Stage and Status that merges those dimensions, and a second called Type that just notes whether the data rows are coming from the Stage subquery or Status subquery.

           

          Once you have this UNION query, then creating the crosstab is straightforward.

           

          Note that if you do other calculations off this query, such as a SUM of the Total Measure, you will get 2x the expected results because the query is duplicating records. There are workarounds for this (like putting Type on the Filter shelf and just selecting one of the values), however I'd suggest using the original datasource for that portion of your analysis, and only use the UNION'd datasource when you need to .

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: How to merge two dimentions into one dimention
            .Eli Goldfarb

            Hi Jonathan,

            Thanks for your response. i wanted to know if it is possible to implement it without a Custom SQL Union query? is it possibe to do it using a cal field?

            Eli

            • 3. Re: How to merge two dimentions into one dimention
              Jonathan Drummey

              Hi Eli,

               

              Attached is a way using calculated fields. The difficulty here is that you have two dimensions, Stage and Status, and not only want to merge them but also count each record twice, once for each instance of the record for Stage and Status. What I did was create six calculated fields, for all the values of Stage and Status, and then used Measure Names as the dimension to bring them all in as Measure Values.

               

              Jonathan

              • 4. Re: How to merge two dimentions into one dimention
                Sonu Sharma

                One way would be use 'Full Outer Join' between 2 dimensions and have a non matching condition and then you can create a calc in tableau to merge them into single column something this.

                Result of Full Outer Join:

                 

                Status Dim 1                Stage Dim 1            Status Dim 2              Stage Dim 2

                A                                  Open                      NULL                          NULL

                NULL                            NULL                       A                               Closed

                 

                Calcs would be :-

                 

                If Status Dim1 Is null

                then Status Dim 2

                Else

                Status Dim1

                 

                create similar calcs for other columns too. I hope it helps.