4 Replies Latest reply: Mar 25, 2014 2:07 AM by Rajni Batra

# Intervel match based on intersaction

Hi,

I sheet1 i have transactional Data and in Sheet2 i have defined some intervals.

Booking Date  in range(Booking From Date , Booking To Date)

Departure Date in range( Departure From Date , Departure To Date)

No Of passengers in Range (No of Passenger From , To)

on basis of these 3 Intervals i want to get Commision value.

But because Min(Booking from Date ) is same. Data is geeting multiplied.

• ###### Re: Intervel match based on intersaction

Hi.

Subtract 1 second from the end (to) dates.

• ###### Re: Intervel match based on intersaction

Check the attached app.

A few combinations of Booking dates and Departure dates have null values as they are not available in the Interval table.

• ###### Re: Intervel match based on intersaction

Hi Rajni,

here is another solution using the extended intervalmatch syntax and avoiding row multiplying

hope this helps

regards

Marco

```tabTransactions:
[Departure Date],
[No_ of Passengers]
FROM
(ooxml, embedded labels, table is Sheet1)
Where [Booking Date] = Date#('01.03.2014', 'DD.MM.YYYY') AND [Departure Date] = Date#('01.03.2014', 'DD.MM.YYYY');

tabIntervals:
[Booking To Date],
[Departure From Date],
[Departure To Date],
[No of Seats From],
[No of Seats To],
[Persentage of Commission]
FROM
(ooxml, embedded labels, table is Sheet2);

Left Join (tabTransactions)
IntervalMatch ([Booking Date])
[Booking From Date] as BFD,
[Booking To Date] as BTD
Resident tabIntervals;

Right Join (tabTransactions)
IntervalMatch ([Departure Date], BFD, BTD)
[Departure From Date] as DFD,
[Departure To Date] as DTD,
[Booking From Date] as BFD,
[Booking To Date] as BTD
Resident tabIntervals;

Right Join (tabTransactions)
IntervalMatch ([No_ of Passengers],BFD, BTD, DFD, DTD)
[No of Seats From] as NoSF,
[No of Seats To] as NoST,
[Departure From Date] as DFD,
[Departure To Date] as DTD,
[Booking From Date] as BFD,
[Booking To Date] as BTD
Resident tabIntervals;

Left Join (tabTransactions)
[No of Seats From] as NoSF,
[No of Seats To] as NoST,
[Departure From Date] as DFD,
[Departure To Date] as DTD,
[Booking From Date] as BFD,
[Booking To Date] as BTD,
[Persentage of Commission] as [Commision value]
Resident tabIntervals;

DROP Fields NoSF, NoST, DFD, DTD, BFD, BTD From tabTransactions;
```

• ###### Re: Intervel match based on intersaction

@marco  : Can you please explain the part where you did right join with interval Match, I was trying with same method but doing left join and that was multiplying the data.

Regards,

Rajni