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 and Join

Hi all,

I have two tables (capacity offer and fact table) which I would like to join.

(see attached excel)

In the capacity offer table I have the planned capacity per workcenter, day and time interval.

In the fact table I have the activities and duration for each activity.


Now I would like to create two new fields in the fact table (capacity offer 1 and capacity offer 2) where it should be checked if capaity is planned (check with table "capacity offer") and if yes the capacity offer information should be calculated: TimestampEndFact minus TimestampStartFact to get the corresponding capacity offer for this event.


Additionally if there is an event in fact table where I do not find a match in capacity offer table, the capacity offer should be 0.

And I have also the case that I have events which which are related to two intervals. Here I would like that it should be allocated to the interval relating to the TimestampStartFact-field. (see line 54 in excel, where TimestampEndFact overlapps by 2 seconds to the next interval).


Technically I think it should work somehow with the intervalmatch funtion, but I have no clue how to connect and calculate the tables correctly.... Do anybody could help me here?


Thanks a lot for your feedback!

Best

John

2 Replies
balar025
Creator III
Creator III

Hi,

Might extended Interval match suffice your problem.

Please find attachment.

Regards,

Ravi Balar

Not applicable
Author

Hi Ravi,

for my example it works! Thanks!

But now I have trouble with data records where I have available capacity, but no facts.

For example if you delete first two data records in fact table, it will start  on 01.11.2016  01:06:48 AM.

From my capacity table I know that I have capacity also availabe for 12:00:00 AM to 01:06:47 AM.


Any idea how to solve this?