-
1. Re: Determining single field value off multiple rows in related data-source
swaroop.gantela Nov 14, 2018 8:24 PM (in response to Jason Ludwig)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 thatbarely 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
-
287487monitor.xlsx 14.2 KB
-
287487monitor.tflx 14.9 KB
-
287487monitor.twbx 43.3 KB
-
-
2. Re: Determining single field value off multiple rows in related data-source
Jason Ludwig Nov 20, 2018 5:15 AM (in response to swaroop.gantela)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 Nov 21, 2018 8:48 AM (in response to Jason Ludwig)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.