Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rajni_batra
Valued Contributor

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.

Please help!!!

1 Solution

Accepted Solutions

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

QlikCommunity_Thread_111673_Pic1.JPG.jpg

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;

QlikCommunity_Thread_111673_Pic2.JPG.jpg

4 Replies
whiteline
Honored Contributor II

Re: Intervel match based on intersaction

Hi.

Subtract 1 second from the end (to) dates.

srchilukoori
Valued Contributor

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

QlikCommunity_Thread_111673_Pic1.JPG.jpg

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;

QlikCommunity_Thread_111673_Pic2.JPG.jpg

rajni_batra
Valued Contributor

Re: Intervel match based on intersaction

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

Community Browser