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

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

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

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.

Name	EEID	Shift 1	Shift 2	Shift 3
Tom	49985	6/15/16	6/17/16	6/19/16
Sally	52636	6/25/16	6/27/16	6/29/16
David	77436	6/27/16		6/30/16
Tina	78895	6/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

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

sunny_talwar — Wed, 29 Jun 2016 15:56:26 GMT

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

tamilarasu — Wed, 29 Jun 2016 15:58:44 GMT

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

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

Anonymous — Wed, 29 Jun 2016 16:08:22 GMT

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

tamilarasu — Wed, 29 Jun 2016 16:14:14 GMT

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