How to select a work crew from an alternative work schedule

fuson — Fri, 06 Nov 2020 13:45:57 GMT

Hello, I am a beginner qlikview and having trouble selecting a work crew from an alternative work schedule. Here is the schedule:

Monday 6am to 5:59pm = Crew A, 6pm to 5:59am = Crew C
Tuesday 6am to 5:59pm = Crew A, 6pm to 5:59am = Crew B
Wednesday 6am to 5:59pm = Crew A, 6pm to 5:59am = Crew B
Thursday 6am to 5:59pm = Crew A, 6pm to 5:59am = Crew B
Friday 6am to 5:59pm = Crew C, 6pm to 5:59am = Crew B
Saturday 6am to 5:59pm = Crew C, 6pm to 5:59am = Crew SS
Sunday 6am to 5:59pm = Crew SS, 6pm to 5:59am = Crew C

Here's what I have tried:

=If(Match([Day_Name],'Mon'),
If(Match([SHIFT],2),'A-Crew','C-Crew'),
If(Match([Day_Name],'Tue','Wed','Thu'),
If(Match([SHIFT],2),'A-Crew','B-Crew'),
If(Match([Day_Name],'Fri'),
If(Match([SHIFT],2),'C-Crew','B-Crew'),
If(Match([Day_Name],'Sat'),
If(Match([SHIFT],2),'C-Crew','SS-Crew'),
If(Match([Day_Name],'Sun'),
If(Match([SHIFT],2),'SS-Crew','C-Crew'))))))

but since the late crew spills into the next day, the values are wrong.

Any help would be appreciated. ☺

Re: How to select a work crew from an alternative work schedule

Anil_Babu_Samineni — Fri, 06 Nov 2020 14:56:32 GMT

Perhaps this?

If(WeekDay(FieldName)='Mon' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew A',
If(WeekDay(FieldName)='Mon' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew C',
If(WeekDay(FieldName)='Tue' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew A',
If(WeekDay(FieldName)='Tue' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew B',
If(WeekDay(FieldName)='Wed' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew A',
If(WeekDay(FieldName)='Wed' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew B',
If(WeekDay(FieldName)='Thu' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew A',
If(WeekDay(FieldName)='Thu' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew B',
If(WeekDay(FieldName)='Fri' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew C',
If(WeekDay(FieldName)='Fri' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew B',
If(WeekDay(FieldName)='Sat' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew C',
If(WeekDay(FieldName)='Sat' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew SS',
If(WeekDay(FieldName)='Sun' and Time(FieldName,'hh:mm')>='06:00' and Time(FieldName,'hh:mm')<='17:59', 'Crew SS,
If(WeekDay(FieldName)='Sun' and Time(FieldName,'hh:mm')>='18:00' and Time(FieldName,'hh:mm')<='05:59', 'Crew C'))))))))))))))

topic Re: How to select a work crew from an alternative work schedule in QlikView

How to select a work crew from an alternative work schedule

Re: How to select a work crew from an alternative work schedule