2 Replies Latest reply on Mar 22, 2012 8:47 AM by Tandra Bidyananda

    Dynamic filtering and data blending/joining

    Tandra Bidyananda
    Tandra Bidyananda Mar 19, 2012 2:37 PM

      Hi,

       

      For the sample data attached, I am trying to use the filters in the "Specific_code" sheet to decide how to blend data (using defined relationships) for the "All_codes" sheet. My main purpose with this workbook is to dynamically filter on a certain code in the "Specific_code" sheet, and have this action update/filter the data in "All_codes" sheet using the 2 blended fields, "After_time" and "Before_time".

       

      I can achieve a similar result using the Custom SQL below.

       

       

      SELECT

        "A".*,

        "B".*

       

      FROM

       

      (SELECT

        "X"."ID" AS "ID",

        DATE("X"."TIME") AS "DATE",

        "X"."TIME" AS "TIME",

        "X"."CODE" AS "CODE"

      FROM "ADMIN"."X" "X"

      ) "A"

       

      INNER JOIN

       

      (SELECT

        "X"."ID" AS "ID",

        "X"."CODE" AS "CODE",

        "X"."TIME" AS "TIME",

        DATE("X"."TIME") as "DATE",

        MIN("Y"."TIME" - interval '1 hour' ) as "BEFORE_TIME",

        MIN("Y"."TIME" + interval '1 hour' ) as "AFTER_TIME"

      FROM "ADMIN"."X" "X"

        WHERE "CODE"='1569.31'

      GROUP BY

        "X"."ID",

        "X"."CODE",

        DATE("X"."TIME")

      ) "B"

       

      ON "A"."ID" = "B"."ID" AND "A"."DATE"= "B"."DATE"

       

      WHERE "A"."TIME" BETWEEN "B"."BEFORE_TIME" AND "B"."AFTER_TIME"

       

      Is there anyway to achieve the same in Tableau?

       

      Thanks,

       

      Tandra

        • 1. Re: Dynamic filtering and data blending/joining
          Tracy Rodgers
          Tracy Rodgers Employee Mar 21, 2012 10:06 AM (in response to Tandra Bidyananda)

          Hi Tandra,

           

          I have reattached your workbook with what I think is the desired outcome. Look at Sheet5. Click on one of the Codes and it will filter the All_Codes worksheet. To do this, a couple things needed to be done. First, more relationships needed to be made between the data sources. To see which ones were added, go to Data-->Edit Relationships.

           

          Then, a filter action was added to Sheet5 and was directed at the All_Codes worksheet; a specific field was added to filter on Code. I hope this helps!

           

          -Tracy

          • 2. Re: Dynamic filtering and data blending/joining
            Tandra Bidyananda
            Tandra Bidyananda Mar 22, 2012 8:47 AM (in response to Tracy Rodgers)

            Hi Tracy,

             

            What I am trying to do is based on the filtered code in "Specific Code", let us say 1569.31, I want to see all the records with all codes that happened on the same date and Id combinations.

             

            So if I filter for 1569.31, then I should see all codes for the Id and date associated with the 2 records that pop up, i.e. Id=X1 and dates= 1/14 and 2/24. With the blending I want to pull in the before_time and after_time columns for the 1569.31 records into the "All_codes" table.

             

            Attached is an excel workbook of how I want this to look like. The records in Sheet 1 with "Final_selection"=Y are the records I need. Hope it makes more sense.

             

            Thanks!

             

            Tandra