Colour highlight rows dependent on conditions/critiera

[#1]

Hi,

I have a straight table that looks as below, and I need to add some conditions and (if possible) highlight the rows Green/Red/Amber dependent on the following criteria. If it is not possible to highlight the rows, perhaps add a new column as 'RAG Status' and have G/R/A inputted depending on the result.

Criteria:

GREEN - Actual Start/End Time is less than 5 minutes of the Scheduled Start/End Time

AMBER - Actual Start/End Time is more than 5 and less than 30 minutes of the Scheduled Start/End Time

RED - Actual Start/End Time is more than 30 minutes of the Scheduled Start/End Time

Table:

Name	Description	Scheduled Start Time	Scheduled End Time	Actual Start Time	Actual End Time
Run1	ABC	07:30:00	07:45:00	07:34:00	07:46:00
Run2	123	18:00:00	18:20:00	18:15:00	18:24:00
Run3	XYZ	09:45:00	10:05:00	09:55:00	10:22:00
Run4	456	12:05:00	12:30:00	12:05:00	13:08:00

So in this instance, the results would be as follows:

Run1 - Green - Times are within the criteria

Run2 - Amber - Starts more than 5 minutes after Scheduled Start Time

Run3 - Amber - Starts and Ends more than 5 minutes after Scheduled End/Start Times

Run4 - Red - Ends more than 30 minutes after Scheduled End Time

Thanks and kind regards,

Tristan

[sunny_talwar]

Alright I seem to be running out of ideas. Can you try this

NewTable:
LOAD*,
If(Frac([Actual Start Time]) - Frac([Scheduled Start Time]) < (5/(24*60)) and Frac([Actual End Time]) - Frac([Scheduled End Time]) < (5/(24*60)), 1,
If(Frac([Actual Start Time]) - Frac([Scheduled Start Time]) > (30/(24*60)) or Frac([Actual End Time]) - Frac([Scheduled End Time]) > (30/(24*60)), 3, 2)) as ColorFlag,
Interval(Frac([Actual Start Time]) - Frac([Scheduled Start Time]), 'hh:mm:ss') as Check1,
Interval(Frac([Actual End Time]) - Frac([Scheduled End Time]), 'hh:mm:ss') as Check2
RESIDENT Temp_ActualData;
DROP Table Temp_ActualData;

[#3]

Ha that's understandable! Frustrating that we can't isolate today's results. There must be a way to do it. The Frac addition returns 00:00:00 across the Check1 and Check2 fields

[sunny_talwar]

That is weird. Frac should not have returned 00:00:00. It seems that Actual End Time is not time field. You might have to force them to be time

Try this :

Scheduled_Data:

LOAD A as Name,

  B as Description,

  Intreval#(C, 'hh:mm:ss') as [Scheduled Start Time], // use the format your C is in and do that for all the times.

  D as [Typical Duration],

  E as [Buffer Time],

  F as RunDay,

  G as [Batch Type],

  H as [Job Count Required],

  I as [Scheduled End Time],

  1 as Schedule

FROM

[..\DataSources\RBTSCHPEXT.xlsx]

(ooxml, no labels);

Actual_Data:

LOAD A as Name,

  C as Status,

  D as [Status Message],

  H as [Actual Start Time],

  Date#(I,'1YYMMDD') as [Actual Start Date],

  J as [Actual End Time],

  Date#(K,'1YYMMDD') as [Actual End Date],

  G as Job_ID,

  RecNo() as ID

FROM

[..\DataSources\RBTMSG.xlsx]

(ooxml, no labels);

[#5]

Hi Sunny,

The Interval didn't work, but I tried this for each of the time occurrences:

time(time#(text( num(C,'000000')),'hhmmss'),'hh:mm:ss') as [Scheduled Start Time],

And as far as I can tell this has now worked! It seems to be responding as expected, which is fantastic. Thank you for all the help - really appreciate it. If I can give you official feedback somehow please let me know.

Best,
Tristan

[sunny_talwar]

Awesome

I am glad you were able to figure it out. No need for a formal feedback, I would just ask you to mark the correct answer and any helpful answers (if any) so that anybody looking at this long thread in the future can find this discussion useful.

Best,

Sunny

[sunny_talwar]

Did not realized that you already marked the correct answer.