topic Re: Intervel match based on intersaction in QlikView

Intervel match based on intersaction

rajni_batra — Mon, 24 Mar 2014 08:30:09 GMT

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.

Please help!!!

Re: Intervel match based on intersaction

whiteline — Mon, 24 Mar 2014 20:41:09 GMT

Hi.

Subtract 1 second from the end (to) dates.

Re: Intervel match based on intersaction

srchilukoori — Mon, 24 Mar 2014 21:06:43 GMT

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

MarcoWedel — Mon, 24 Mar 2014 21:53:20 GMT

Hi Rajni,

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

hope this helps

regards

Marco

tabTransactions:
LOAD [Booking Date], 
     [Departure Date], 
     [No_ of Passengers]
FROM
[http://community.qlik.com/servlet/JiveServlet/download/493690-98169/Test.xlsx]
(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:
LOAD [Booking From Date], 
     [Booking To Date], 
     [Departure From Date], 
     [Departure To Date], 
     [No of Seats From], 
     [No of Seats To], 
     [Persentage of Commission]
FROM
[http://community.qlik.com/servlet/JiveServlet/download/493690-98169/Test.xlsx]
(ooxml, embedded labels, table is Sheet2);

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

Right Join (tabTransactions)
IntervalMatch ([Departure Date], BFD, BTD)
LOAD Distinct
  [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)
LOAD Distinct
  [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)
LOAD Distinct
  [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

rajni_batra — Tue, 25 Mar 2014 06:07:16 GMT

Thank you all for your replies. It was really helpful..

@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