5 Replies Latest reply on Dec 7, 2016 5:44 AM by John Sobczak

    Filtering Using Blended Data

    Chris Sawyer

      The story:  I have two different data sources.  One has less data (like specific, high priority events) and another has more data (every event including those in the first source).  With both data sources, I have a serial number (as in a car or machine), date, start time, end time (no end time in the secondary source), latitude, and longitude.

       

      What I've done:  I have a map of high priority events and a map of low priority events.  Both are on a dashboard.  The high priority event map is the main view.  I have a filter action set up specifically on serial and date; it set to exclude when clearing the selection.  The result is when I click on a specific data point from the priority events, a "sub-map" pops up on the dashboard isolating all the events that occurred for that serial and that day.  When I clear the selection, the sub-map disappears.  This works great.

       

      What I want is this:  when I click a data point on the priority events map, the sub-map pops up and shows events for that serial, that day, and only when the secondary source's start time is between the start and end time of the priority event.  In other words:  here's the main event.  Now what else happened between the start and end time of the main event?

       

      The issue:  I've tried to create a filter that results in a "true/false" statement, plug it into the filter shelf, and only select "true".  However, the old "blending with more data on one than the other" has bit me and is causing nulls.  Is there anyway to use a "greater than" relationship in a filter action?  Is there any way to accomplish this with R code using a calculated field?  I've loaded R, have it set up, and have done some basic functions with it successfully.  However, I'm a newb at R, so I don't know the syntax.  I'm more of an SQL guy.

       

      My attempt at the filter (while already blending on serial and date):

      IF (ATTR([Serial Number]) = ATTR([Source1].[Serial Number]) AND ATTR([Start Date]) = ATTR([Source1].[Load Date]) AND ATTR([Start Time]) >= ATTR([Source1].[Start Time]) AND ATTR([Start Time]) <= ATTR([Source1].[End Time])) THEN 'TRUE' ELSE 'FALSE' END

       

      I can do this in Excel using if statements nested in match statements entered as an array formula.  Surely, I can manage this in Tableau.  Unless, Tableau has figured out dynamic parameters, please don't suggest parameters.  My data changes every 15 minutes on an API feed.

       

      Thanks!!!

        • 1. Re: Filtering Using Blended Data
          John Sobczak

          My initial thought is if your data sets are that similar why don't you union them on the database backend (or even via Tableau connection using custom sql) and use an added column identifier for the two sets of records.  Life would be much easier.

          • 2. Re: Filtering Using Blended Data
            Chris Sawyer

            John - thanks for the quick response.  I have union'd the two sources with the column identifier on custom SQL.  However, I think I've exhausted my creativity because I still can't seem to figure it out.  A calculated field is only going to evaluate it's own record.  I need it to scan the entire column in the data set determine whether or not the time is greater than or equal to the selected record's time.  I've seen the RAWSQLAGG functions, but they don't work with my datasource.  Fixed LOD only works with aggregated functions, right?

            • 3. Re: Filtering Using Blended Data
              John Sobczak

              Can you provide a simple mock-up in Excel with a dozen or so records?  I can attempt to work through it.

              • 4. Re: Filtering Using Blended Data
                Chris Sawyer

                John - I've included a table with one machine for one day (150 records).  The primary records I describe above would be from the source "Load Counts".  The secondary source would be from "Asset Operations".  I highlighted an example in yellow.  As you can see, the primary event of LOADING occurred between 2:05P and 2:11P.  That is broken down in the asset operation showing 2:05P-2:09P IDLING and 2:09P-2:11P WORKING.  I need to click on the primary event and be able to see the other events.

                • 5. Re: Filtering Using Blended Data
                  John Sobczak

                  Chris, I'm so sorry for never responding to this.  I did spend some time on it when you first sent it and I could not come up with anything either, but that's not to say it's still not possible.   If you start a new post with this data you might get some fresh eyes looking at.