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]

Yes it could be, but why join extra stuff together if we are not going to use it, its inefficient. We should only join things which we will use for calculation in your case. Try this: (Highlighted the changed part in Red) and note that you may have to change things around in your where statement. We can work on that later if the output is not as expected.

Scheduled_Data:
LOAD A as Name,
B as Description,
C as [Scheduled Start Time],
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);

Temp_ActualData:

LOAD Name,

          [Actual Start Time],

          [Actual End Time],

Resident Actual_Data

Where [Actual Start Date] = Today() and [Actual Start Date] = Today();

Inner Join(Temp_ActualData)
LOAD Name,         
          [Scheduled Start Time],
          [Scheduled End Time]

          Scheduled_Data

Where [Scheduled Start Date] = Today() and [Scheduled Start Date] = Today();;

NewTable:
LOAD *,

          If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,

          If([Actual Start Time] - [Scheduled Start Time] > (30/(24*60)) or [Actual End Time] - [Scheduled End Time] > (30/(24*60)), 3, 2)) as ColorFlag

RESIDENT Temp_ActualData;

DROP Table Temp_ActualData;

[#3]

Ah right I see, that makes sense. I loaded the following change, which is the same as yours without the Where statement in the Inner Join - as Scheduled Start/End Date does not exist. I've been getting the same results as before however:

Temp_ActualData:
LOAD Name,
[Actual Start Time],
[Actual End Time]
Resident Actual_Data
Where [Actual Start Date] = Today() and [Actual End Date] = Today();
 
Inner Join(Temp_ActualData)
LOAD
Name,
[Scheduled Start Time],
[Scheduled End Time]
Resident Scheduled_Data;

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

[sunny_talwar]

Well we need to restrict the Scheduled data to be restricted for a today. Because if we don't do that then we may have multiple entries for Scheduled Table if the Name is repeated in there. Is there any other date field which can be restricted for today?

[#5]

That should be okay because the Scheduled Table only contains a static list of ~100 Names, none of which are repeated. It's within the Actual Data table that we have multiple entries of the same Name. There's only two date fields unfortunately; the Actual Start/End Date.

[sunny_talwar]

Can you post the logfile for when you run your script?? I want to see if the Where statement is getting honored or not. Send 2 logfile, one with the above script and one with this one:

Temp_ActualData:
LOAD Name,
[Actual Start Time],
[Actual End Time]
Resident Actual_Data
Where Floor(Num([Actual Start Date])) = Floor(Num(Today())) and Floor(Num([Actual End Date])) = Floor(Num(Today()));
 
Inner Join(Temp_ActualData)
LOAD
Name,
[Scheduled Start Time],
[Scheduled End Time]
Resident Scheduled_Data;

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

Leaving for work now, will continue this discussion in an hour or so. Till then you will have enough time to run the two scripts and generate two logfiles.

Best,

Sunny

[#7]

I've attached the two log files (test1 being the previous script, test2 with your Where update). Thanks very much for the continued support.

Best,

Tristan

[sunny_talwar]

Changing directions here Tristan. I think the joins are not working for us and we need to take an alternative route. Lets try it with Mapping Load. Here is the script that you need to try:

Scheduled_Data:

LOAD A as Name,

  B as Description,

  C as [Scheduled Start Time],

  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);

MappingTable1:

Mapping

LOAD Name,

  [Scheduled Start Time]

Resident Scheduled_Data;

MappingTable2:

Mapping

LOAD Name,

  [Scheduled End Time]

Resident Scheduled_Data;

NewTable:

LOAD *,

  If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,

  If([Actual Start Time] - [Scheduled Start Time] > (30/(24*60)) or [Actual End Time] - [Scheduled End Time] > (30/(24*60)), 3, 2)) as ColorFlag,

  Interval([Actual Start Time] - [Scheduled Start Time], 'hh:mm:ss') as Check1,

  Interval([Actual End Time] -[Scheduled End Time], 'hh:mm:ss') as Check2;

LOAD Name,

  [Actual Start Time],

  [Actual End Time],

  ApplyMap('MappingTable1', Name) as [Scheduled Start Time],

  ApplyMap('MappingTable2', Name) as [Scheduled End Time]

RESIDENT Actual_Data

Where Floor(Num([Actual Start Date])) = Floor(Num(Today())) and Floor(Num([Actual End Date])) = Floor(Num(Today()));

See if this helps. and then I can suggest few things to refine your script.

Best,

Sunny

[#9]

Hi Sunny,

Thanks for the new script. This too is returning the same results however; the Check1 and Check2 still have the cumulative times. I keep trying different things on top of this but I can't find a way to just get today's time. I've attached the logfile of that run.

Best,

Tristan

[sunny_talwar]

Check1 and Check2 hasn't changed from the inner join approach?? They are still showing the exact same values?? Can you make the following change to your script:

NewTable:

LOAD *,

  If([Actual Start Time] - [Scheduled Start Time] < (5/(24*60)) and [Actual End Time] - [Scheduled End Time] < (5/(24*60)), 1,

  If([Actual Start Time] - [Scheduled Start Time] > (30/(24*60)) or [Actual End Time] - [Scheduled End Time] > (30/(24*60)), 3, 2)) as ColorFlag,

  Interval([Actual Start Time] - [Scheduled Start Time], 'hh:mm:ss') as Check1,

  Interval([Actual End Time] -[Scheduled End Time], 'hh:mm:ss') as Check2,

Num([Actual Start Time]) as NumActualStart,

Num([Actual EndTime]) as NumActualEnd,

Num([Scheduled Start Time]) as NumScheduledStart,

Num([Scheduled End Time]) as NumScheduledEnd;

LOAD Name,

  [Actual Start Time],

  [Actual End Time],

  ApplyMap('MappingTable1', Name) as [Scheduled Start Time],

  ApplyMap('MappingTable2', Name) as [Scheduled End Time]

RESIDENT Actual_Data

Where Floor(Num([Actual Start Date])) = Floor(Num(Today())) and Floor(Num([Actual End Date])) = Floor(Num(Today()));

Now create a table box with Name, NumActualStart, NumActualEnd, NumScheduledStart, NumScheduledEnd, ColorFlag, Check1, Check2, [Actual Start Time], [Actual End Time], [Scheduled End Time], [ScheduledStartTime]

[#11]

When I reload the script (even with the new Num changes) the Check1/Check2 give the same values as before:

Could this have anything to do with the raw data? I currently add in a new dimension for all of the Time fields to convert the raw data e.g. =time(time#(text( num([Scheduled Start Time],'000000')),'hhmmss'),'hh:mm:ss'). So the raw time would look like 1146 instead of 00:11:46

Below is a screen shot of the new table box: