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

[sunny_talwar]

Would be possible for you to share a sample to check this?

[Anonymous]

Sunny,

I'm trying to create a new QVW with inline load statements but am not sure how much data to include.  If these are all the fields that are included in the code above, do you need anything else?

[sunny_talwar]

Would you be able to provide this in text format instead of an image?

[vinieme12]

Try this

=pick(wildmatch(OnTrackStatus,'On*','Off*'),LightGreen(),LightRed())

[Anonymous]

LOAD * INLINE [

    ScheduledShiftDate, ShiftKey, OnTrackStatus

    42537, 10968_42537, On Track

    42544, 10968_42544, Off Track

    42551, 10968_42551, On Track

    42559, 10968_42559, Off Track

    42562, 10968_42562, On Track

];

LOAD * INLINE [

    ShiftDate, ShiftKey, Shift_ShiftTypeName

    42537, 10968_42537, Trainee

    42544, 10968_42544, Trainee

    42551, 10968_42551, Trainee

    42559, 10968_42559, Trainee

    42562, 10968_42562, Trainee

    42565, 10968_42565, Trainee

    42568, 10968_42568, Trainee

    42573, 10968_42573, Trainee

    42575, 10968_42575, Trainee

    42576, 10968_42576, Trainee

];

[Anonymous]

This did not show a different result.  In the screenshot from my first post, all of the shifts are highlighted green even though there are no 'OnTrackStatus' results for shifts 4-8.

Since I'm not using ShiftDate or ShiftKey as a dimension in the chart, do I need to do anything different to relate the on track status for the same shift as the shift being displayed?  As you can see in the code above (first snippet) I have tried to include ScheduledShift = ShiftDate without any luck.

Thank you

[vinieme12]

Your EmployeeID should be associated to ShiftKey(ShiftKey >> Shifts) and ShiftKey to ShiftStatus

[vinieme12]

I used you inline load and was able to use the same expression

But i also had to add EEID manually.

LOAD * INLINE [

    ScheduledShiftDate, ShiftKey, OnTrackStatus

    42537, 10968_42537, On Track

    42544, 10968_42544, Off Track

    42551, 10968_42551, On Track

    42559, 10968_42559, Off Track

    42562, 10968_42562, On Track

];

LOAD * INLINE [

    ShiftDate, ShiftKey, Shift_ShiftTypeName,EEID

    42537, 10968_42537, Trainee ,10968

    42544, 10968_42544, Trainee,10968

    42551, 10968_42551, Trainee,10968

    42559, 10968_42559, Trainee,10968

    42562, 10968_42562, Trainee,10968

    42565, 10968_42565, Trainee,10968

    42568, 10968_42568, Trainee,10968

    42573, 10968_42573, Trainee,10968

    42575, 10968_42575, Trainee,10968

    42576, 10968_42576, Trainee,10968

];

[Anonymous]

Here is the relationship between the two datasets in my original qvw.  I made a second file with what I saw as the minimum amount of data to trigger the result I wanted, maybe I forgot something in my test data that is causing my issue.

Shifts:
NoConcatenate
Load
  ScheduledShift_Key,
  num(EmployeeID) & '_' &  Num(StartDateTime) as ShiftKey,
  ScheduledFileName,
  ShiftLongName as Shift_ShiftLongName, 
  Date(StartDateTime ) as Shift_StartDateTime, 
  Date(EndDateTime ) as Shift_EndDateTime, 
  ShiftTypeName as Shift_ShiftTypeName,
  Shift_PersonGUID as PersonGUID
Resident ShiftHistory_Trainee;

OnTrackStatus:
Left Join(Shifts)
LOAD
  Employee_ID & '_' & Num(Shift_Date) as ShiftKey,
     Date(Shift_Date) as 'Shift_Date', 
     On_Track_Status
From [..\QVD\OnTrackStatus.qvd](qvd);

Both EmployeeID fields originate from the same location, but we didn't want Qlik to join the two tables on the Employee ID field already loaded from it's original source (what feeds ShiftHistory_Trainee).  The QVD is created from an MS Access database which loads it's Employee ID values from the same SQL database ShiftHistory_Trainee comes from originally.

Is there any chances there's a datatype mismatch causing my problems?  I've tried a few variants of num(Employee_ID) and not.

Thank you for the help,

Phil