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

    Intervel match based on intersaction

    Rajni Batra

      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 _

          Hi.

           

          Subtract 1 second from the end (to) dates.

          • Re: Intervel match based on intersaction
            Sadha Chilukoori

            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
              Marco Wedel

              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