2 Replies Latest reply on Jun 21, 2016 9:26 AM by philip.thornton

    Join two data sources based on d1.timestamp between d2.start and d2.end

    philip.thornton

      Is this possible?

       

      I have two data sources (both generated by stored procedures).

       

      Data source #1 contains several headers. The only two fields I want to join on are "start" and "end" (both datetime fields). We'll call this data source d1.

      Data source #2 contains only one field named "minutes" which is also datetime. We'll call this data source d2.

       

      I want to do a join like: d1.minutes between d2.start and data2.end

       

      There are places where Tableau allows you to join exact fields from each data source however I'm not finding where I can do this between statement.

       

      When I try to create a calculated field to link the two, I'm able to create something like this however when I attempt to use it, Tableau tells me "Fields cannot be used from the ___ data source, because there is no relationship to the primary data source. In the data window, switch to the ___ data source and click at least one link icon to blend the data sources."

       

      IF ATTR([Minutes]) >= ATTR([usp_levelOfUnitsAvail].[start])

      AND ATTR([Minutes]) < ATTR([usp_levelOfUnitsAvail].[end])

      THEN "Show"

      END

       

      The end goal is I will be showing events that occurred on every minute for the last week. I generate every minute timestamp for the last week then wanting to join it so I can graph etc accordingly.

       

      I have

        • 1. Re: Join two data sources based on d1.timestamp between d2.start and d2.end
          Mahfooj Khan

          Hi Philip,

           

          To blend two data sources you should have at least one common field in both the sources. That field will be your blending key.

          Data type of the field should be same. As you said you wants to blend data source 1 with data source 2 where you have Minute field in DS1 and Start and End Date in DS2. Both the sources are belongs to Datetime attribute. BUT units of the fields are not same one is minutes and second is date. Using datediff() convert Start and End Date in minutes. So that you can blend with primary data source (minute field). Go to Data menu> edit relations and use custom to add the joining conditions.

           

          I've tried to create a sample workbook as per your data structure. Have a look.

          To blend I've create a calc field to get minute from Start and End date using Datediff().

          After blending

          Workbook (version 9.2) attached for your reference. Feel free to ask If you've any question. I dont know how much this help though you can get some Idea.

           

          Mahfooj

          • 2. Re: Join two data sources based on d1.timestamp between d2.start and d2.end
            philip.thornton

            I should have stated that my Minutes field was also a datetime. With all fields being datetime format, I was trying to join two data sources with no other fields other than timestamp A is between timestamp B and timestamp C.

             

            No worries, I think I may have found another way to display the data that will work for me. Thank you for your time.

             

            Philip