Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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]);




1 Solution

Accepted Solutions
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Florry,

see the attached.

I've not needed to use an intervalmatch here.

Marcus

View solution in original post

14 Replies
Anonymous
Not applicable
Author

Hello,

can you share some data?

Thanks!

Not applicable
Author

Hi Elena, thanks for your reply.

here is some sample data:

company  startendfactor
a22:016:001
a6:0116:002
b16:0122:00

3

 

ID    start activity  end activity
3458342015.01.05 12:332015.01.05 15:44
3458352015.01.05 06:052015.01.05 17:00
3458362015.01.05 13:002015.01.05 16:00
3458372015.01.05 14:122015.01.05 17:12
3458382015.01.05 03:002015.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)

Anonymous
Not applicable
Author

Florry

Do activities always start and end on the same day ?

Not applicable
Author

Hi Bill, thanks for your reply!! Activities can also overlap days

Anonymous
Not applicable
Author

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!

Not applicable
Author

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.

     

IDstart activityend activitycompanyfactorhours
3458382015.01.05 03:002015.01.05 06:00a13
3458382015.01.05 06:012015.01.05 16:00a210
3458382015.01.05 16:012015.01.05 22:00b36
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Florry,

see the attached.

I've not needed to use an intervalmatch here.

Marcus

Anonymous
Not applicable
Author

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

Not applicable
Author

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  startendfactorweekday
a22:0106:001Mo
a22:0106:002Tue
a06:0116:002Wed
b16:0122:003Thu
a06:0122:004Sat
b22:0100:004Sat
a00:0106:005Sun