8 Replies Latest reply on Nov 1, 2016 7:48 AM by Nandhakumar Ramanathan

    Get Subquery results directly in Tableau

    sanjeeth.ch

      Hi Team,

      This is simple example but i am not sure how to get the same output in Tableau (I dont want to use custom SQL).

       

      SQL Query: Only one table and i have different statuses like 2,4 and 14. I would like to achieve the ID's which are having status with 14 are also passed though 2,4 statuses.

       

      select * from Table1 where status in (2,4)

      and ID in (select ID from Table1 where status = 14)

       

      Table1
      IDStatus
      12
      24
      32
      44
      52
      62
      72
      84
      94
      104
      114
      214

       

      Please provide the suggestions to achieve the results.

       

      Thanks...

        • 1. Re: Get Subquery results directly in Tableau
          Daniel Vincent

          Create a custom calc off the dimension in your source table.  The IF statement with an OR should get you what you want.  The REGEXP will substitute as your IN.

           

          IF REGEXP_MATCH([Status],'2|4')=True OR

          [Status]=14 THEN 'Show' ELSE 'Hide' END

           

          Then put that on the filter shelf. 

          1 of 1 people found this helpful
          • 2. Re: Get Subquery results directly in Tableau
            Carl Slifer

            Howdy Sanjeeth,

             

            It is a bit confusing to see what you're after here so I'm providing two solutions.

             

             

            {FIXED [ID]: SUM(IF [Status] = 14 THEN 1 ELSE 0 END)} >= 1

            Create the above calculation and place it on the filters shelf and only keep true. This will return all statuses and ID pairs for any ID that was 14 at some point

             

             

            {FIXED [ID]: SUM(IF [Status] = 14 THEN 1 ELSE 0 END)} * MAX({FIXED [ID]: SUM(IF [Status] = 2 THEN 1 ELSE 0 END)},{FIXED [ID]: SUM(IF [Status] = 4 THEN 1 ELSE 0 END)})

            Create the above calculation on the filters shelf and only keep true. Th will return only the IDs that have status that were at some point either (2 or 4) and 14 at another point.

             

             

            In this case both return the same result because your data set is not disparate enough.

             

             

             

            Cheers!

            Carl Slifer

            InterWorks

            • 3. Re: Get Subquery results directly in Tableau
              kettan

              Blending the data source with itself is another way to do this as shown in GIF below.

              That said, it is probably better to use the solution  already shared by Carl.

               

              thread 203936 Get Subquery results directly in Tableau.gif

               

              Ps. This technique has previously been shared in  Re: how to filter one item and view all matches

               

              Ps. You might like to up-vote  Correlated Subquery

               

              Ps. See links to similar questions (and their answers) in  above mentioned idea

               

              Attached Workbook Version:  9.0

              .

              • 4. Re: Get Subquery results directly in Tableau
                sanjeeth.ch

                Hi Carl Slifer,

                 

                Thanks for replying... I did't understand the above Calculation, i tried put it in tableau and it's not giving me option to select true or false. when i drag the this calculated field its giving me how to filter the value based on SUM, AVG, COUNT...etc.

                 

                I created the calculation what you have given me but i see that status is varchar field,

                Let me put my question in this way.

                I would like to achieve below query results

                 

                select count(*) from Table1 where status in ('Active','Pending')

                and ID in (select ID from Table1 where status = 'Transfer')

                 

                "In this status 2 means Active and status 4 means pending and 14 status means Transfer."

                  

                IDStatus
                1Active
                2Pending
                3Active
                4Pending
                5Active
                6Active
                7Active
                8Pending
                9Pending
                10Pending
                1Transfer
                2Transfer

                 

                 

                If i execute the above query i get two records that is

                 

                IDStatus
                1Active
                2Pending

                 

                 

                Thanks,

                Sanjeeth

                • 5. Re: Get Subquery results directly in Tableau
                  sanjeeth.ch

                  It has more than 1 Million records and i don't want to blend the data to achieve this sub query results.

                   

                  Thanks for sharing this....

                  • 6. Re: Get Subquery results directly in Tableau
                    kettan

                    Understandable 

                     

                    Just so you know, Carl's formula works fine for me in Tableau 9.0 as shown in GIF below:

                     

                    thread 203936 Get Subquery results directly in Tableau - FIXED.gif

                    • 7. Re: Get Subquery results directly in Tableau
                      Nandhakumar Ramanathan

                      Hi Carl,

                       

                      How can i achieve this in tableau. Can you please help me here?

                       

                      SELECT

                      DATEPART(YEAR,pre_process_start) AS Y_ORDER,

                      DATEPART(MONTH,pre_process_start) AS M_ORDER,

                      SUBSTRING(CAST(CAST(pre_process_start AS DATETIME) AS VARCHAR), 1, 3) AS M,

                      Count(session_id) as Tot_request

                      from [approp].[APLUS_PRPTY_16] APP

                      INNER JOIN [UW_SEERI].[approp].[CustomerInfo] CI ON APP.bill_subscriber =CI.subscriber_nbr

                      WHERE bill_subscriber not in ('90019633637Q', 'RETROFARMERS', '001X0000CAPP', '001XXFARMERS')

                      AND app.session_id in (select session_id from [approp].[APLUS_PRPTY_16]  where seq_nbr =2 AND (SUBSTRING(bill_subscriber,1,3)!='041' AND  SUBSTRING(bill_subscriber,12,1)!='R'))

                      GROUP BY DATEPART(YEAR,pre_process_start ),DATEPART(MONTH,pre_process_start ),SUBSTRING(CAST(CAST(pre_process_start  AS DATETIME) AS VARCHAR), 1, 3)

                       

                      Regards,

                      Nandha

                      • 8. Re: Get Subquery results directly in Tableau
                        Nandhakumar Ramanathan

                        Can someone help me here?

                         

                        Regards,

                        Nandha