hour split

[#1]

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?

[#2]

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

[its_anandrjs]

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

[sunny_talwar]

Can you share your updated app?

[sunny_talwar]

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

[#6]

Sunny,

Here is the updated app

[#7]

Thank you so much sunny