Forum Discussion
Filling a column following specific rules
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
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")
1 Reply
- amitchandak
Super User
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")