5 Replies Latest reply on Jul 14, 2017 7:26 AM by Michael Gillespie

    create dimension with multiple measure

    Aym cham

      HI all,

       

      In my Table, il have:

       

      Two dimensions: date and name

      five measures: x1,x2,x3,x4,x5

       

      I want to create two calculate field, the first is a dimension which contains x1, x2, x3, x4, x5 and the second one is a measure with the values of x1,x2,x3,x4,x5.

       

      The solution is to do a pivot but i work with a database and it's imposible to do this.

       

      Anyone have a solution?

       

      thanks in advance.

       

      Aym.

        • 1. Re: create dimension with multiple measure
          Michael Gillespie

          I'll ask the usual question: why is a pivot impossible?  Is the data set too large?

           

          There are very few good solutions to this: it's a data configuration issue more than a Tableau problem.

           

          Can you do the pivot with Initial or Custom SQL?  Can you create a view on the database that does the pivot?

          • 2. Re: create dimension with multiple measure
            Aym cham

            hi Michael,

             

            It's impossible to do a pivot because i work with an database and i read that it's impossible to do the pivot such as the excel file.

             

            But it's possible to do a pivot with custom sql but i don't know how can i do this...

             

            Can you help me to do the custom sql?

             

            thank you?

             

            Aym.

            • 3. Re: create dimension with multiple measure
              Michael Gillespie

              Ah, yes, that is true.

               

              Have you looked at this document?

              Pivot Data from Columns to Rows

              Scroll down to the section that describes how to do the pivot with Custom SQL.  You'll have to adapt it to your specific needs but it should get you started.

              • 4. Re: create dimension with multiple measure
                Aym cham

                Thank you Michael.

                 

                if i understand to do the pivot of this table i do this custom sql ?

                 

                 

                Select [date],[name]

                , [x1]  as [pivot field]

                from [Table]

                Union All

                Select [date],[name]

                ,[x2] as [pivot field]

                from [Table]

                Union All

                Select [date],[name]

                ,[x3] as [pivot field]

                from [Table]

                Union All

                Select [date],[name]

                ,[x4] as [pivot field]

                from [Table]

                Union All

                Select [date],[name]

                ,[x5] as [pivot field]

                from [Table]

                i don't know if it's possible to do multiple "union all".

                 

                Can you correct me please?

                 

                Table:

                 

                datenamex1x2x3x4x5
                1/07/2017test1118234
                1/07/2017test1217498
                1/07/2017test131661212
                1/07/2017test141581716
                1/07/2017test1613122624
                1/07/2017test17121430,528
                1/07/2017test1811163532
                1/07/2017test15141021,520
                1/07/2017test19101839,536
                2/07/2017test2109204440
                2/07/2017test21182248,544
                2/07/2017test2127245348
                2/07/2017test21362657,552
                2/07/2017test2145286256
                2/07/2017test21543066,560
                2/07/2017test2163327164
                2/07/2017test21723475,568
                2/07/2017test2181368072
                • 5. Re: create dimension with multiple measure
                  Michael Gillespie

                  That LOOKS right, but the only way to tell is to test it!

                   

                  I'm not a SQL expert, so take that as my best guess.