topic Select shift patterns in QlikView

Select shift patterns

Tue, 01 Nov 2016 15:43:13 GMT

Hi Community

I have a time field, I need to split it up into 3 shifts. Here is an example (I have tried a few variations of this, including converting to number). Maybe ive just been looking at this forf 2 long, seems straightforward, but not working.

if (Num#([Time Processed],'h:mm:ss') >= '07:00:00' and Num#([Time Processed],'h:mm:ss') <= '15:00:00', 'DayShift', 'Test') as Shift1,

if (Num#([Time Processed],'h:mm:ss') >= '07:00:00' and Num#([Time Processed],'h:mm:ss') <= '15:00:00', 'DayShift', 'Test') as Shift1,
if ([Time Processed] >= '07:00:00' and [Time Processed] <= '15:00:00', 'DayShift', 'Test') as Shift2,
if (Num#([Time Processed],'#,##0') >= 0.291666666666667 and (Num#([Time Processed],'#,##0')) <= 0.625, 'DayShift', 'Test') as Shift3

Am I losing the plot?

Thanks in advance community

Peter

Re: Select shift patterns

johnw — Tue, 01 Nov 2016 23:49:25 GMT

One danger would be if [Time Processed] is actually a timestamp or some other format instead of a time. If it's really a time, maybe one of these, and fiddle with the numbers and literals as suits you.

if([Time Processed]<=maketime(8),'Early',if([Time Processed]<=maketime(16),'Day','Night')) as [Shift]

if(hour([Time Processed])<=8,'Early',if(hour([Time Processed])<=16,'Day','Night')) as [Shift]

Re: Select shift patterns

MarcoWedel — Wed, 02 Nov 2016 00:15:46 GMT

Hi,

maybe one solution might be:

LOAD [Time Processed],
    If([Time Processed] >= '07:00:00' and [Time Processed] < '15:00:00', 'DayShift',
        If([Time Processed] >= '15:00:00' and [Time Processed] < '23:00:00', 'SwingShift',
          If([Time Processed] >= '23:00:00' and [Time Processed] < '24:00:00' or [Time Processed] >= '00:00:00' and [Time Processed] < '07:00:00', 'NightShift'
      ))) as Shift
Inline [
Time Processed
1:02:03
2:03:04
3:04:05
4:05:06
5:06:07
6:07:08
7:08:09
8:09:10
9:10:11
10:11:12
11:12:13
12:13:14
13:14:15
14:15:16
15:16:17
16:17:18
17:18:19
18:19:20
19:20:21
20:21:22
21:22:23
22:23:24
23:24:25
];

hope this helps

regards

Marco

Re: Select shift patterns

MarcoWedel — Wed, 02 Nov 2016 00:36:08 GMT

maybe also possible:

tabShifts:
LOAD Dual(Shift,RowNo()) as Shift Inline [
Shift
DayShift
SwingShift
NightShift
];

tabShiftTimes:
LOAD [Time Processed],
    Mod(Floor([Time Processed]-'07:00:00','08:00:00')*3,3)+1 as Shift
Inline [
Time Processed
1:02:03
2:03:04
3:04:05
4:05:06
5:06:07
6:07:08
7:08:09
8:09:10
9:10:11
10:11:12
11:12:13
12:13:14
13:14:15
14:15:16
15:16:17
16:17:18
17:18:19
18:19:20
19:20:21
20:21:22
21:22:23
22:23:24
23:24:25
];

regards

Marco

Re: Select shift patterns

Wed, 02 Nov 2016 09:45:41 GMT

Hi John

Thanks for getting back to me, ,[Time Processed] = <Time Processed, datetime,> I don't want to update the DB, the hour expression above worked fine so thanks a lot.

Peter

Re: Select shift patterns

Wed, 02 Nov 2016 09:49:24 GMT

Hi Marco

Thanks for the response, I have used inline Load in the past to manually sort data, not 100% sure the times below would fit into my model, would I have to enter every possible time within 24hr timeframe or does the above code somehow take this into account?

Thanks

Peter

Re: Select shift patterns

johnw — Wed, 02 Nov 2016 15:59:39 GMT

It was just sample data to show that his expression produced the intended results. You wouldn't use an inline load at all.