3 Replies Latest reply on Nov 21, 2018 8:48 AM by swaroop.gantela

    Determining single field value off multiple rows in related data-source

    Jason Ludwig

      I have 3 data-sources (excel worksheets) in my workbook:

      1. A list of monitors which contains details and properties pertaining to the monitor's settings and the service in which is being monitored.  Each row has a unique ID field.
      2. A list of maintenance periods, with starting and ending timestamps for each.  Each row has a unique ID and a reference to the PK ID field from the monitor list.
      3. A list of alerts (downtime), with starting and ending timestamps for each.  Each row has a unique ID and a reference to the PK ID field from the monitor list.

       

      For example:

      Monitor IDMonitor NameMonitor ServiceMonitor Admin
      457DB Login Page CheckProduct DatabaseGroup 1
      836APP Login Page CheckProduct Web FrontendGroup 2
      1215LB Login Page CheckProduct Load BalancingGroup 3

       

      Maintenance IDStart DateTimeEnd DateTimeMonitor ID
      SDT103867/29/2018  8:00:00 AM7/29/2018  11:00:00 AM457
      SDT101457/20/2018  3:00:00 PM7/29/2018  8:00:00 AM836
      SDT102797/28/2018  11:00:00 PM7/29/2018  8:00:00 AM1215

       

      Alert IDStart DateTimeEnd DateTimeMonitor IDMaintenance?
      SV22355487/29/2018  8:17:41 AM7/29/2018  11:01:42 AM457should reflect that 1:42 was outside maintenance window
      SV22353797/20/2018  2:10:12 PM7/29/2018  8:30:41 AM836should reflect that 1:20:29 was outside maintenance window
      SV22357917/28/2018  11:30:39 PM7/29/2018  7:01:41 AM1215should reflect that 100% of this alert was within maintenance window

       

      This is how the data is organized in my excel worksheets.  So you can see that both the alert and maintenance data-sources have a many-to-one relationship to the monitors data-source where a single monitor can have multiple alerts and multiple maintenance periods.

       

      What I need to figure out is the portion of the alert period that occurred within or outside of a maintenance period.  Does anyone know how I can figure this out? Should I even try to do this in Tableau, or should I make this calculation in excel before the data-source loading?

        • 1. Re: Determining single field value off multiple rows in related data-source
          swaroop.gantela

          Jason,

           

          I think it can be done in Tableau with judicious filtering of the joins.

           

          I performed the joins in Tableau Prep.

          I joined AlertsSheet to MaintenanceSheet on MonitorID

          and then joined that result to MonitorSheet on MonitorID

           

          I tried adding a few alerts, but wasn't sure how you wanted to handle them.
          I made one alert outside of any maintenance window, and another that

          barely overlapped a maintenance window. I filtered out the former using:

           

          [Start DateTime Alert]>[End DateTime Maint]

          OR

          [End DateTime Alert]<[Start DateTime Maint]

           

          and kept the latter. But maybe you want to keep all even if completely

          outside a maintenance window?

           

          I calculated the uncovered portion on the start side using:

          IF [Start DateTime Alert]>[Start DateTime Maint] THEN 0

          ELSE DATEDIFF('second',[Start DateTime Alert],[Start DateTime Maint])/86400

          END

           

          and similarly for the uncovered end portion:

          IF [End DateTime Alert]<[End DateTime Maint] THEN 0

          ELSE DATEDIFF('second',[End DateTime Maint],[End DateTime Alert])/86400

          END

           

           

          This all may be completely off base.

          Please see workbook v10.3, tflx, and xlsx attached in the Forum Thread:

          Determining single field value off multiple rows in related data-source

          1 of 1 people found this helpful
          • 2. Re: Determining single field value off multiple rows in related data-source
            Jason Ludwig

            Thanks for the effort, but I'm having trouble on the joins.  In my data-set, monitors will have alerts and maintenance periods (rows) occurring at different time periods.  When I join alerts to maintenance (on the monitor id) I get alerts being joined to maintenance periods that occur at different times. swaroop.gantela

            • 3. Re: Determining single field value off multiple rows in related data-source
              swaroop.gantela

              Jason,

               

              I'm not sure that I caught the drift, but I think the idea would be to join all alerts to all maintenance periods

              (for one monitor) and then filter down the set to just those that are of interest.

               

              Understandably, if your tables are prohibitively large, then this join would be suboptimal for performance.

              But to reduce this, further conditions could be added to the join (only those on the same day, and even

              same hour).

               

              Would be grateful if you would adjust the previously attached xlsx to more closely match your scenario.