Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
iamscribe
New Contributor III

First Selected Value - Second, Third, Fourth, etc values

Hello,

I'm working to keep track of the days my trainees have each of their training shifts.  Each trainee is expected to complete a certain number of training shifts and our daily shift history reload contains all shifts published 'tomorrow' so that Qlik is always receiving the most up to date schedule prior to that day starting.

This is the desired output.

NameEEIDShift 1Shift 2Shift 3
Tom499856/15/166/17/166/19/16
Sally526366/25/166/27/166/29/16
David774366/27/166/30/16
Tina788956/29/16

I've pulled the first shift date using this expression:

=if(Shift_ShiftTypeName='Trainee',(FirstSortedValue(Shift_StartDateTime,Shift_StartDateTime)))

Using that syntax, I tried this to try and get the 4th record:

=if(Shift_ShiftTypeName='Trainee',(FirstSortedValue(Date(Shift_StartDateTime),Shift_StartDateTime,4)))

Am I going about this the right way?  For some of my users, their records pull up just fine and in order.  For some of the people, it will show a record for Shift 1, 2, nothing for shift 3, 4, then have a record for shift 5.  I don't really understand how it can know what record 1, 2, and 5 should be without knowing what records 3 and 4 are.

Thank you for the direction, happy to provide any further details.

Phil

Tags (1)
1 Solution

Accepted Solutions

Re: First Selected Value - Second, Third, Fourth, etc values

Try like below,

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,2),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,3),'MM/DD/YYYY')

View solution in original post

4 Replies

Re: First Selected Value - Second, Third, Fourth, etc values

Why don't you try with Max instead of FirstSortedValue:

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 2))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 3))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 4))

=If(Shift_ShiftTypeName='Trainee', Max(Shift_StartDateTime, 5))

Re: First Selected Value - Second, Third, Fourth, etc values

Try like below,

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,2),'MM/DD/YYYY')

=Date(Max({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,3),'MM/DD/YYYY')

View solution in original post

iamscribe
New Contributor III

Re: First Selected Value - Second, Third, Fourth, etc values

Thank you for the help, I tried this instead and it worked as intended.

=Date(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,1),'MM/DD/YYYY')

=Date(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,2),'MM/DD/YYYY')

=Date(Min({<Shift_ShiftTypeName={'Trainee'}>}Shift_StartDateTime,3),'MM/DD/YYYY')

Re: First Selected Value - Second, Third, Fourth, etc values

Yes. You are right.  We need to use Min in this case as per your sample. Have a nice day.