Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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;
Hi.
Subtract 1 second from the end (to) dates.
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.
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;
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