Case statement..

[Anonymous]

Hi,

I have been trying to figure out a way to calculate the out of a column depending on the values of other columns.

From the table below I would like to create an expression to say:

case if Level = 'AOG' and DateTime is between 18:00 and 08:00 (following morning) then 'AOG OO'

     or if Level = 'AOG' and DateTime is on a Weekend then 'AOG OO' Else Level

Customer	Level	DateTime
A	AOG	2018-05-01 09:08:06
B	Routine	2018-05-03 13:23:04
B	Routine	2018-05-07 19:11:07
A	AOG	2018-05-11 23:30:02

OUTPUT REQUIRED:

Customer	OriginalLevel	NewLevel	DateTime
A	AOG	AOG	2018-05-01 09:08:06
B	Routine	Routine	2018-05-03 13:23:04
B	Routine	Routine	2018-05-07 19:11:07
A	AOG	AOG OO	2018-05-11 23:30:02

This calculation is being done to work out any levels that fall into out of hours (out of normal working hours).

Thank you in advance for your assistance.

stalwar1‌youssefbelloum‌

[sasikanth]

Try this,

IF(Level = 'AOG'  and  (hour(DateTime)>18 and hour(DateTime)< 8  Or (weekday(DateTime)='Sat' or weekday(DateTime)='Sun'),   'AOG OO' , Level )

Thanks,

[Anonymous]

Hi Sasi,

This now works for weekends but still does not work for the time period.

[Anonymous · Accepted solution]

Thanks all, I managed to get it working by doing the following:

=IF(PriorityLevel = 'AOG' and (hour(OutTimestamp)>18 and hour(OutTimestamp)<24) Or (AMPM = 'AM' and hour(OutTimestamp)<8) Or (weekday(OutTimestamp)='Sat' or weekday(OutTimestamp)='Sun'),'AOG OOH',PriorityLevel)

[Anil_Babu_Samineni]

Please mark you response as correct answer, Then?