Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]);
Hi Florry,
see the attached.
I've not needed to use an intervalmatch here.
Marcus
Hello,
can you share some data?
Thanks!
Hi Elena, thanks for your reply.
here is some sample data:
company | start | end | factor |
a | 22:01 | 6:00 | 1 |
a | 6:01 | 16:00 | 2 |
b | 16:01 | 22:00 | 3 |
ID | start activity | end activity |
345834 | 2015.01.05 12:33 | 2015.01.05 15:44 |
345835 | 2015.01.05 06:05 | 2015.01.05 17:00 |
345836 | 2015.01.05 13:00 | 2015.01.05 16:00 |
345837 | 2015.01.05 14:12 | 2015.01.05 17:12 |
345838 | 2015.01.05 03:00 | 2015.01.05 22:00 |
Example: 345838 starts at 3 am (company a, factor 1, until 6am), next a new tax factor is applied until 16:00 am (10 hours), after that a new company starts for the remaining 6 hours (company b, factor 3)
Florry
Do activities always start and end on the same day ?
Hi Bill, thanks for your reply!! Activities can also overlap days
Ok! Can you also write the expected results for these examples, in order to understand how to calculate the proportions of factors and the correct assignment of the activity to company?
thanks!
Sorry, i forgot. This is an idea of the result. Each activity and its time is broken down by the matching intervalls from the first table.
ID | start activity | end activity | company | factor | hours |
345838 | 2015.01.05 03:00 | 2015.01.05 06:00 | a | 1 | 3 |
345838 | 2015.01.05 06:01 | 2015.01.05 16:00 | a | 2 | 10 |
345838 | 2015.01.05 16:01 | 2015.01.05 22:00 | b | 3 | 6 |
Hi Florry,
see the attached.
I've not needed to use an intervalmatch here.
Marcus
Dear Florry,
I'm not be able to help you..
I found a solution but it doesn't handle activities in multiple days. I use just the sample data you gave us.
Furthermore, there is a little problem with ID 345836 (start time 13:01 while the correct value is 13:00).
Anyway, I attach my qvw, hoping can help you as an idea.
KR
Elena
Hey Marcus,
thank you soo much, i highly appreciate your solution and it works!! This is pure QlikView sorcery!!
One more question (i hardly dare not to ask after so much work) but: is it possible to include weekdays into the tax table (first one)? Taxes are also calculated based on different days. For example, activities on Monday (starting at 22:01 until 06:00am on Tuesday) have a different tax factor than activies on Tuesday (starting at 22:01 until 06:00am on Wednesday).
company | start | end | factor | weekday |
a | 22:01 | 06:00 | 1 | Mo |
a | 22:01 | 06:00 | 2 | Tue |
a | 06:01 | 16:00 | 2 | Wed |
b | 16:01 | 22:00 | 3 | Thu |
a | 06:01 | 22:00 | 4 | Sat |
b | 22:01 | 00:00 | 4 | Sat |
a | 00:01 | 06:00 | 5 | Sun |