Highlighting expression columns

[Anonymous]

Hello,

After the group here helped solve this issue for me - First Selected Value - Second, Third, Fourth, etc values I have run into a new question.  When we pull in shift records, we have a unique key that is created (ShiftKey) that is a combination of the employee ID and the date the shift started since the same person can't be signed up for two shifts that start in one day.  We use the shift key to match up their shift history records and their training evaluation records.  When we display a person's shift records, we want to conditionally highlight the text of the shift date based on whether that shift's evaluation was the trainee was on track of off track.

Off Track - Red

On Track - Green

No Status - Blue

I have tried to get this to work either through the Text Color options on the Shift (X) expression.  I think the issue is that since ShiftDate or ShiftKey are not dimensions in the chart, it isn't able to reliably link the shift date to the evaluation of that shift:

=if(num(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1)=num(min(Shift_Date),1)),
(if(Left(On_Track_Status,3)='Not',LightRed(),
if(Left(On_Track_Status,2)='On',LightGreen(),
if(IsNull(On_Track_Status)>0,LightBlue())))))

Also tried:

=if(num(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1)),
(if(Left(On_Track_Status,3)='Not',LightRed(),
if(Left(On_Track_Status,2)='On',LightGreen(),LightBlue()))))

And:

=if(Left(On_Track_Status,3)='Not',LightRed(),
if(Left(On_Track_Status,2)='On',LightGreen(),LightBlue()))

Training Evaluation Table

ScheduledShiftDate	ShiftKey	OnTrackStatus
42537	10968_42537	On Track
42544	10968_42544	Off Track
42551	10968_42551	On Track

Shift History Table

ShiftDate	ShiftKey
42537	10968_42537
42544	10968_42544
42551	10968_42551
42559	10968_42559

Expected Output

EEID	Shift 1	Shift 2	Shift 3	Shift 4	Shift 5
10968	42537	42544	42551	42559

I have tried joining the Evaluation History table to the Shift History table, but that didn't get me very far.

This is what I currently see, even though there are no Status records for the shifts that haven't occurred yet - the shifts are still highlighting due to the expression above.

Thank you for the assistance!

Phil

[Anonymous]

Any ideas after my last post?  This seems to work fine if I make it out of a pivot table and Shift Date is across the top.  The problem with that direction is that when your table shows 50 people and shifts span a 3 month time frame, there are ~90 columns in the table for all the various possibilities.

Thank you

[vinieme12]

Don't  Left Join to your Employee Table, Do a Crosstab load of your Shifttable so all your shift details are in one column.

[vinieme12]

can you post an excel sample to show how your data tables look like

[Anonymous · Accepted solution]

I was able to figure it out using this code:

=Pick(Wildmatch(FirstSortedValue({<Shift_ShiftTypeName={'Trainee'}>}On_Track_Status,Shift_StartDateTime,1),'Not*','On*'),LightRed(),LightGreen())

Switching the offset number based on the shift # did the trick.

Thank you for the help!

[Anonymous]

Hi Phillip,

We're glad to hear you were able to figure out your solution. Please take a moment to mark a few replies as Helpful to give points to those who assisted. See this document for more information on this: Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Qlik Community Team