6 Replies Latest reply on Feb 1, 2017 2:59 AM by Jim Dehner

    Join on same table

    Sanjay Mistry

      Table Prescription has mainly columns - Drug Name and Generic Name, State, drug cost, number of claims etc.

       

      In the original query they had join on the same table with

      a.drugname = b.genericname and a.state=b.stage. Please know that a and b refers to the same table, Prescription.

       

      The reason they are doing this way is that some drugs are know by the brand name, say Lipitor. But it also has generic name. We want to find out what is the most prescribed medication in each state. Hence, we have to take into consideration both - drug name and generic name.

       

      Left is drug name, and the column on the right is a generic name.

       

      In SQL it is easy to have inner join and create a table on the fly to represent the data. How do I achieve the same in Tableau?

       

      Thanks,

        • 1. Re: Join on same table
          Jamieson Christian

          Sanjay,

           

          No problem. Just connect to your data source and then drag the relevant table/sheet out onto the "playing field" twice. The second time you drag it out, Tableau will establish a join between the two (identical) tables, and you can specify the criteria to use for the join. See example below (based on a quick clipboard-based datasource I threw together):

           

           

          Hopefully this helps? If not, we'll probably need more detail about your requirements and the structure of your data (and a sample would be useful).

          • 2. Re: Join on same table
            Veronica Simoes

            Hi Sanjay

             

            If I understood , you can use group to generic columns,  combine too columns or join the same table

            Could you share the results you want in excel file and workbook sample ?

             

            Bests

            4 of 4 people found this helpful
            • 3. Re: Join on same table
              Jim Dehner

              Hi

              I have done something like this

              The steps followed are:

              1. on the data pane duplicate your data source
              2. then go to DATA in the top ribbon and open the Edit Relationships on the drop down
              3. On the screen you will see a pane that show the primary and secondary files defined and a table of Automatic links
                1. click on the Custom radio button
                2. then highlight all of the links and hit remove
                3. 3 click on the ADD button
                4. then identify the NAME of the field in the 2 data sources you want to link

               

              Let me know if that did what you want

               

              Jim

              • 4. Re: Join on same table
                Sanjay Mistry

                I thought of doing this but wouldn't it create lot of duplicate columns?

                • 5. Re: Join on same table
                  Sanjay Mistry

                  Here is the details on the project: https://cloud.google.com/bigquery/public-data/medicare#what_is_the_most_prescribed_medication_in_each_state

                   

                  Below is the query that I am trying to produce in Tableau:

                   

                  SELECT
                    A
                  .state,
                    drug_name
                  ,
                    total_claim_count
                  ,
                    day_supply
                  ,
                    ROUND
                  (total_cost_millions) AS total_cost_millions
                  FROM (
                   
                  SELECT
                      generic_name
                  AS drug_name,
                      nppes_provider_state
                  AS state,
                      ROUND
                  (SUM(total_claim_count)) AS total_claim_count,
                      ROUND
                  (SUM(total_day_supply)) AS day_supply,
                      ROUND
                  (SUM(total_drug_cost)) / 1e6 AS total_cost_millions
                   
                  FROM
                     
                  `bigquery-public-data.medicare.part_d_prescriber_2014`
                   
                  GROUP BY
                      state
                  ,
                      drug_name
                  ) A
                  INNER JOIN (
                   
                  SELECT
                      state
                  ,
                      MAX
                  (total_claim_count) AS max_total_claim_count
                   
                  FROM (
                     
                  SELECT
                        nppes_provider_state
                  AS state,
                        ROUND
                  (SUM(total_claim_count)) AS total_claim_count
                     
                  FROM
                       
                  `bigquery-public-data.medicare.part_d_prescriber_2014`
                     
                  GROUP BY
                        state
                  ,
                        generic_name
                  )
                   
                  GROUP BY
                      state
                  ) B
                  ON
                    A
                  .state = B.state
                   
                  AND A.total_claim_count = B.max_total_claim_count
                  ORDER BY
                    A
                  .total_claim_count DESC
                  LIMIT
                   
                  5;

                  • 6. Re: Join on same table
                    Jim Dehner

                    Actually no. It's just like using 2 data sources. You use the data from

                    each source for its own purpose independently

                    Jim

                     

                    On Jan 31, 2017 7:24 PM, "Sanjay Mistry" <tableaucommunity@tableau.com>