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

[jsingh71]

Follow this:

for every Dimension or expression give the condition like

if( Starts>5, Green()) ("Give your actual expression its a just example only")as so on.

[alexandros17]

You cannot do in a table, use instead a straight table, in the expressions there are , click and in the background or text item menu add your condition

E.g.: if a<b, RGb(255,0,0), if(a < c, rgb(0,255,0), rgb 100,100,100))

[#4]

Hi Tristan,

You can do this simply with 'visual Cues' tab.

Click right mouse on the table,

select properties,

select visual cues,

and you can introduce the logic a an expression. and select the color criteria as per the requirement.

The 'apply'

Rgds.

Priyantha.

[sunny_talwar · Accepted solution]

Looking for this:

Try this script to create color flag

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

FROM

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

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

and then use this to give color to each of your dimension and expression. Attaching the qvw for reference purposes.

HTH

Best,

Sunny

[#6]

Hi Sunny,

Thanks very much for this! It's really close to what I'm going for. I have three questions:

1) Unfortunately my Actual Start/End Times and Scheduled Start/End Times are from different tables so the lines in the script do not work - is there anyway I can reference two of the columns from a different table?

e.g.

Scheduled_Data:
LOAD A as Name,
B as Description,
C as [Scheduled Start Time],
D as [Scheduled End Time],

and

Actual_Data:
LOAD A as [Actual Start Time],
B as [Actual End Time], 

2) For the second line in the script, I need it to be Amber if the Actual Start OR End Times are >5 of the Scheduled Start/End Times - and for Red the same -  Actual Start OR End Time is more than 30 minutes of the Scheduled Start/End Time

3) Could you explain the formula (5/(24*60)?

Any further help would be really appreciated

Best,

Tristan

[sunny_talwar]

1) Do you have some common fields between the first and second table? I am sure you do, else the comparison just won't make sense. If my assessment is right, just join the two tables together and then do the calculation in a combined table:

TableA:

LOAD *

FROM xyz

Join(TableA)

LOAD *

FROM abc

NewTable:

LOAD *,

          aboveCalculation

Resident TableA;

DROP Table TableA;

2)So Amber is the last part of the nested if statement, if Start Difference is less than 5 and End Difference is less than 5 do red. second if is if Start Difference is greater than 30 and End difference is greater than 30 do red. and if both statements are not true than do Amber which is the scenario you explained above

3) (5/(24*60)) is the number equivalent of 5 minutes

I hope all of the above make sense. Let me know if you still have questions.

Best,

Sunny

[#8]

Hi Sunny,

Thanks again for your reply.

1) That's correct, there is a common field (Name), but I still can't get it to work. It looks something like this:

Scheduled_Data:
LOAD A as Name,
B as Description,
C as [Scheduled Start Time],
D as [Scheduled End Time],

;

Actual_Data:
LOAD A as Name,

A as [Actual Start Time],

B as [Actual End Time], 

;

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

But this returns an error on finding Actual Start Time and Actual End Time. Do you know where I've gone wrong with it?

2) Sorry I think I haven't explained the criteria very well. I was hoping to have it so:

Green if the Start and End differences were <5

Amber if the Start or End differences were >5

Red if the Start or End differences were >30

3) Ah okay I see! So if I wanted to change it to >10 minutes, I would just change the formula to (10/(24*60))?

Thanks again for the help.

Kind regards,

Tristan

[sunny_talwar]

1)

Try this script:

Scheduled_Data:

LOAD A as Name,
          B as Description,
          C as [Scheduled Start Time],
          D as [Scheduled End Time],

Join (Scheduled_Data)
LOAD A as Name,

          A as [Actual Start Time],

          B as [Actual End Time],

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

3) You are bang on target with this one.

2) I will get back to you on this one after testing it out.

Best,

Sunny

[#10]

I think this maybe works for the 2nd question

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,
If([Actual Start Time] - [Scheduled Start Time] > (5/(24*60)) or [Actual End Time] - [Scheduled End Time] > (5/(24*60)), 2,))) as ColorFlag