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]

Sure, I would suggest you to test it out and see if it actually give you the results you are expecting. Good job Tristan‌

I am going to wait for you to let me know if it worked or not, until I put further time on it.

Best,

Sunny

[#3]

Cheers Sunny. I'm having trouble  testing that version; with the original I'm getting duplicates which I can't seem to remove. E.g.

Name       ColorFlag

Run1        1

Run1        2

Run1        3

Run2        1

Run2        2

Run2        3

etc.

I can't figure out what is causing this. This happens when I add the ColorFlag dimension into a table of any type. Any ideas what might be causing that?

Best,

Tristan

[sunny_talwar]

Can you share the script you are using?

[#5]

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


Inner Join(Actual_Data)
LOAD
A as Name,
C as [Scheduled Start Time],
I as [Scheduled End Time]

FROM
[..\DataSources\RBTSCHPEXT.xlsx]
(ooxml, no labels);


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] > (5/(24*60)) and [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag
RESIDENT Actual_Data;

[sunny_talwar]

Try this:

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


Inner Join(Actual_Data)
LOAD
A as Name,
C as [Scheduled Start Time],
I as [Scheduled End Time]

FROM
[..\DataSources\RBTSCHPEXT.xlsx]
(ooxml, no labels);


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] > (5/(24*60)) and [Actual End Time] -[Scheduled End Time] > (5/(24*60)), 3, 2)) as ColorFlag
RESIDENT Actual_Data;

DROP Table Actual_Data;

[#7]

Worked perfectly, thanks Sunny!

So my final issue now is just getting the logic right. My version doesn't seem to have worked. I'm trying to get the following:

Green if the Start and End differences are <5 minutes

Amber if the Start or End differences are >5

Red if the Start or End differences are >30

Making it slightly more complicated; if the Actual Start/End Time is less than the Scheduled Time, it can be green. I.e.

Name	Description	Scheduled Start Time	Scheduled End Time	Actual Start Time	Actual End Time
Run1	ABC	07:30:00	07:45:00	06:00:00	06:15:00
Run2	123	18:00:00	18:20:00	18:15:00	19:24:00

So in this example Run1 would be Green, as starting and ending early is not an issue, but Run2 would be Red for ending more than 30minutes late.

Thanks for all the help with this so far Sunny, really appreciate it.

Best,

Tristan

[sunny_talwar]

This seems to work:

Table:

LOAD Name,

    Description,

    [Scheduled Start Time],

    [Scheduled End Time],

    [Actual Start Time],

    [Actual End Time],

    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

FROM

[https://community.qlik.com/thread/169976]

(html, codepage is 1252, embedded labels, table is @2);

[sunny_talwar]

Actual less than Scheduled is not a problem because negative number will be smaller than (5/(24*60)) and changed the and to or on the second if statement and seems to work.

HTH

Best,

Sunny

[#10]

Ah right okay thanks! I'm still trying different things but changing the and to or didn't seem to work, as below:

[sunny_talwar]

These are all the case for the first if right? where actual is less than scheduled. Those should all be green?