topic Re: hour split in QlikView

hour split

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have the Hour field which has values from 00 to 23.

I need to split this hour into two shifts.

Shift1 : 06 to 20

Shift2 : 19 to 13 (this 13 is next day)

I tried with the below condition in the script but it is not working as expected.

IF(Hour >='06' and Hour<='20','Shift1',

IF(Hour >='19' or Hour <='13','Shift2')) AS SHIFTS,

Can someone please help me with the script?

Re: hour split

sunny_talwar — Fri, 18 Aug 2017 13:58:04 GMT

These shifts are overlapping?

Re: hour split

Fri, 18 Aug 2017 14:07:58 GMT

Yes Sunny

Re: hour split

sunny_talwar — Fri, 18 Aug 2017 14:09:30 GMT

So somebody coming in at 19:30 will have both Shift1 and Shift2? Is that what you are looking to do?

Re: hour split

Fri, 18 Aug 2017 14:20:16 GMT

Not exactly.

People who work at shift1 may extend their timings.

But there were two actual shifts.

Re: hour split

Fri, 18 Aug 2017 17:30:00 GMT

Hi Experts,

i have attached a sample of my data. please help.

Re: hour split

sunny_talwar — Fri, 18 Aug 2017 17:35:15 GMT

Looks good, what is the issue?

Re: hour split

Fri, 18 Aug 2017 17:39:01 GMT

Sunny,

Shift1 is ok. but if you see Shift2, it is not having all the hours between 19 and 13.

i know it will be overlapping. but that is what the requirement.

Re: hour split

sunny_talwar — Fri, 18 Aug 2017 17:41:40 GMT

You will have to concatenate instead of using if here

Table:

LOAD ...,

'Shift 1' as Shift

FROM ...

Where Hour >='06' and Hour<='20';

Concatenate (Table)

LOAD ...

'Shift 2' as Shift

FROM

Where Hour >='19' or Hour <='13';

Re: hour split

its_anandrjs — Fri, 18 Aug 2017 17:45:26 GMT

Your if condition is wrong it is working for Shift1 only because second condition is overlapping so better you can find another way on script part.

I did not get your file once get i let you know.

Regards,

Anand

Re: hour split

Fri, 18 Aug 2017 17:57:35 GMT

Thank you so much Sunny.

One more request, i would like to arrange the hours of Shift2 from 19 to 13 rather than in ascending or descending.is there a way to do?

currently it is automatically ascending like below

Re: hour split

its_anandrjs — Fri, 18 Aug 2017 18:00:02 GMT

Try this ways also

NoConcatenate

TimeNew:

LOAD *,

'06 - 20 (Shift1)' AS SHIFTHOURS_New

Resident Time

Where Hour >= '06' and Hour <= '20';

Concatenate(TimeNew)

LOAD *,

'19 - 13 (Shift2)' AS SHIFTHOURS_New

Resident Time

Where Hour >= '19' or Hour <= '13';

DROP Table Time;

DROP Field SHIFTHOURS;

Note:- But how you handle >= 19 hours in the SHIFTHOURS_New please check this

Regards

Anand

Re: hour split

sunny_talwar — Fri, 18 Aug 2017 18:06:55 GMT

Can you share your updated app?

Re: hour split

sunny_talwar — Fri, 18 Aug 2017 18:23:59 GMT

Try this as your expression

Dual(Num(Sum(TotalCount), '#,##0.00'), If(SHIFTHOURS = '19 - 13 (Shift2)',

Match(Hour, '19', '20', '21', '22', '23', '00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13'),

Match(Hour, '06', '07', '08', '09', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')))

and expression default formatting

Re: hour split

Fri, 18 Aug 2017 18:26:34 GMT

Sunny,

Here is the updated app

Re: hour split

Fri, 18 Aug 2017 18:51:56 GMT

Thank you so much sunny