Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Colour highlight rows dependent on conditions/critiera

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:

NameDescriptionScheduled Start TimeScheduled End TimeActual Start TimeActual End Time
Run1ABC07:30:0007:45:0007:34:0007:46:00
Run212318:00:0018:20:0018:15:0018:24:00
Run3XYZ09:45:0010:05:0009:55:0010:22:00
Run445612:05:0012:30:0012:05:0013: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

1 Solution

Accepted Solutions
sunny_talwar

Looking for this:

Capture.PNG

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

View solution in original post

55 Replies
jsingh71
Partner - Specialist
Partner - Specialist

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.

color.png

alexandros17
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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

Looking for this:

Capture.PNG

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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