topic Highlighting expression columns in QlikView

Highlighting expression columns

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Highlighting expression columns

sunny_talwar — Fri, 08 Jul 2016 21:25:18 GMT

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

Re: Highlighting expression columns

Anonymous — Sun, 10 Jul 2016 20:13:42 GMT

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?

Re: Highlighting expression columns

sunny_talwar — Mon, 11 Jul 2016 19:55:44 GMT

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

Re: Highlighting expression columns

vinieme12 — Mon, 11 Jul 2016 20:14:55 GMT

Try this

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

Re: Highlighting expression columns

Anonymous — Mon, 11 Jul 2016 20:17:55 GMT

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

];

Re: Highlighting expression columns

Anonymous — Mon, 11 Jul 2016 20:25:07 GMT

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

Re: Highlighting expression columns

vinieme12 — Mon, 11 Jul 2016 20:52:22 GMT

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

Re: Highlighting expression columns

vinieme12 — Mon, 11 Jul 2016 20:59:58 GMT

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

];

Re: Highlighting expression columns

Anonymous — Tue, 12 Jul 2016 04:20:12 GMT

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

Re: Highlighting expression columns

Anonymous — Wed, 20 Jul 2016 22:46:38 GMT

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

Re: Highlighting expression columns

vinieme12 — Thu, 21 Jul 2016 11:55:04 GMT

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

Re: Highlighting expression columns

vinieme12 — Thu, 21 Jul 2016 11:55:50 GMT

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

Re: Highlighting expression columns

Anonymous — Fri, 22 Jul 2016 20:17:25 GMT

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!

Re: Highlighting expression columns

Anonymous — Fri, 22 Jul 2016 21:20:34 GMT

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