2 Replies Latest reply: Jul 17, 2017 8:41 AM by John Sommer RSS

    Intervalmatch and Join

    John Sommer

      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!