Filling a column following specific rules

[pattarom]

Hi

I have a table containing data in this form (simplified):

DateTime	TimeSlot	measure1	measure 2
2022-01-10 00:00:00		3	10
2022-01-10 01:00:00		232	11
2022-01-10 02:00:00		3	4
2022-01-10 03:00:00		4	5

I have many tables of this type (one table for each monitored element), where the DateTime column is starting at the beginning of the year with hourly intervals. Collected data are queued weekly by my colleagues.

Every element has its time slot definition consisting of three levels (high, mid, low) changing by time and day of the week.

An example of a rule is (times are in 24h format): From Monday to Friday, from 00:00 to 07:00 level is low, from 07:00 to 08:00 level is mid, from 08:00 to 17:00 level is high, from 17:00 to 19:00 level is mid and from 19:00 to 00:00 level il low. Saturday, Sunday, and holidays levels are always low.

I need to fill the TimeSlot column with a level, following the rule I wrote in the example.

TimeSlot column will be used later to filter data in reports.

I'm figuring out how to approach this problem.

Thank you in advance.

Michele

[amitchandak · Accepted solution]

@pattarom , You can create a new column like

Switch(True() ,

Weekday([datetime],2) >=6 || Timevalue([DateTime]) <= time(7,0,0) || (Timevalue([DateTime]) > time(19,0,0) ) , "Low"
(Timevalue([DateTime]) > time(7,0,0) && Timevalue([DateTime]) <= time(8,0,0) ) || (Timevalue([DateTime]) > time(17,0,0) && Timevalue([DateTime]) <= time(19,0,0) ) , "Mid" ,
"High")