Hello Gurus,
I have a requirement to show the Data processing in ETL (loads) in a Tableau Dashboard using a chart and put them into three buckets
1. Load Success (condition for this is if there is a Start and End time for Process code and control ID)
2. Load Failure (condition for this should be if there is a start time, 'NO' end time and another process has triggered which means there is a new start time)
3. Load in Progress (if there is a start time but no end time)
A process could be triggered multiple times in a day which means it can fail several times too.
This is how the dataset looks like
Process code Control ID Load Start Time Load End Time
--------------------------------------------------------------------------------
A a1 9/1/2020 0:00 9/1/2020 1:30
A a2 9/1/2020 14:00
A a3 9/1/2020 16:00
B b1 9/1/2020 0:00 9/1/2020 1:30
B b2 9/1/2020 14:00 9/1/2020 14:00
I am using a Gnatt chart to represent this data.
I was able to get this to work for 'Load success' and 'Load in Progress' but I am having issues putting the Load failure scenario.
I am attaching the sample workbook here for convenience.
Any help is greatly appreciated.
Try this:
Status
// Final Status
IF ISNULL([Load Start Time])=FALSE AND ISNULL([Load End Time])=FALSE THEN
"Load Success"
ELSEIF ISNULL([Load Start Time])=FALSE AND ISNULL([Load End Time])=TRUE THEN
// No end time. If last item, then this is the one that's in progress.
IF [Load Start Time] = {FIXED [Process code]: MAX([Load Start Time])} THEN
"Load in Progress"
ELSE
// Previous failed run.
"Load Failure"
END
END
See attached.
