IntervalMatch with different time intervals and conditions
Hello all,
at the moment i'm trying to solve a matching problem between two tables. The first table consists of the company's name, one start date, one end date (both 'hh:mm') and a tax factor (different taxes for different times). The second table has an activity ID, a start and end date of the activity ('YYYY.MM.DD hh:mm') itself.
The main problem is to match activities from the second to the first table by their activity time and to adjust the right tax factor. Additionally there are overlapping activity times, so taxes have to be calculated proportionally.
I'm grateful for every hint to solve it. Best regards!!
FIRST:
LOAD company,
start,
end,
factor
FROM DATA.xlsx (ooxml, embedded labels, table is [1]);
SECOND:
LOAD ID,
[start activity],
[end activity]
FROM DATA.xlsx (ooxml, embedded labels, table is [2]);
When loading the FIRST records, the factors, I've split records that span midnight. So for example,
company start end factor weekday
1 22:01 06:00 1 Mon
becomes
company start end factor weekday
1 22:00 24:00 1 Mon
1 00:00 06:00 1 Tue
Also I've used some modulo arithmetic with lookups on the standard DayNames variable to return the integer value of the WeekDay. This is then used later when joiung FIRST to SECOND for the MATCH table.
Note that I've also amended the start times in FIRST, so for example 22:01 becomes 22:00. This is because the Hours calculation was missing the one minute of 22:00 to 22:01. Similarly I've amended end times of 23:59 to 24:00.
Glad you appreciate my efforts! Interesting problem.
ok, first point to note is that we're splitting records which span midnight.
As noted above, I'm splitting this record
company start end factor weekday
1 22:01 06:00 1 Mon
into
company start end factor weekday
1 22:00 24:00 1 Mon
1 00:00 06:00 1 Tue
This occurs when start > end.
So, when start > end, in the condition below I'm going to create a value of WeekDayIndex for our second weekday (Tue in the example above).
Now, we already have a value on Mon in the weekday field of the record we are reading. In our DayNames variable
SET DayNames = 'Mon;Tue;Wed;Thu;Fri;Sat;Sun';
We can do a lookup using the index function.
So index('$(DayNames)', WeekDay)
For Mon this will return 1, Tue will return 4, Wed will return 7, etc, etc.
What I want to do here is get to a value of 0 for Mon, 1 for Tue, 2 for Wed, etc.
I can do this with modulo arithmetic.
div([result of index], 4)
For Mon this will return 0, Tue will return 1, etc, etc.
Now I need to deal with the fact that we may be creating a record here for our record spanning midnight (if start (22:00) > end (06:00). So I add 1. What do we do though if we're looking at a record which spans midnight on Sunday? Our logic so far would give us an index of 8 which is incorrect. So, we wrap the result in a mod().
When splitting the SECOND table I'm now also checking whether start date differs from end date.
I've also changed the key field from SECOND to MATCH to IDNew. This allows us to differentiate between records on the first day and records on the second day.
