3 Replies Latest reply on Apr 2, 2013 11:38 AM by Jonathan Drummey

    adding distinct to tableau generated query

    hari.nalajala

      Hi,

       

      Is it possible to add distinct clause to tableau generated query without having to use custom SQL option?

      I need to bring in distinct values from database and then create rollups (sum, count, count(distinct) etc) at Tableau level.

       

      Regards,

      Hari

        • 1. Re: adding distinct to tableau generated query
          Joshua Milligan

          Hari,

           

          I'm not sure I exactly follow what you want.  In a sense, you are getting the distinct values for any dimension that you place on the Row or Columns and then an aggregation of any measures.  The actual query generated by Tableau is more likely a GROUP BY instead of SELECT DISTINCT -- but ultimately the result is a distinct list of dimension values. 

           

          But maybe I'm not understanding your requirement.  If so, can you provide an example?

           

          Regards,

          Joshua

          • 2. Re: adding distinct to tableau generated query
            hari.nalajala

            Thanks Joshua.

            I have multiple tables in my query and I need to get the distinct of the result set (I do not have a way to bring in unique records using joins because of the nature of the data I am dealing with) before Tableau can apply group by in order to avoid double counting of some of the measures.

            I am bringing in results at most granular level in order to support my other count(distinct) calculations in the same report.

             

            Please find sample data and expected result below.

             

            Sample  Data
            Contact_IDIndiv_idMRRdivision
            1111$50 Div-1
            1111$50 Div-1
            2112$40 Div-1
            3113$30 Div-2
            4114$20 Div-3
            4114$20 Div-3
            Need to get distinct rows from above data and then sum MRR across distinct rows
            Expected ResultMRR
            DivisionMRR
            Div-190
            Div-230
            Div-320

             

            Regards,

            Hari

            • 3. Re: adding distinct to tableau generated query
              Jonathan Drummey

              Hello Hari,

               

              See the attached. I created a calculated field called "WS Avg MRR" using the formula WINDOW_SUM(AVG(MRR)) with some optimizations. The view is set up with division on Rows, with Contact_ID, and Indiv_id (i.e. all the other fields that define a distinct "row" in the data) on the Level of Detail Shelf. Then the WS Avg MRR field is set up with an Advanced... Compute Using of division, Contact_ID, and Indiv_ID on the right-hand side of the Advanced dialog, with Restarting every set to division.

               

              This causes Tableau to create a "set" of the distinct values of the three fields, and restart the partitioning for each new value of division.

               

              Jonathan