6 Replies Latest reply: Jun 10, 2015 10:38 AM by Stefan Wühl RSS

    IntervalMatch help ?

    Ganesh Reddy

      Hi All,

      How to combine below highlighted two table with interval match

       

      Main_Work_Order:

      Load

           ServiceEventEquipmentId,

           ServiceEventStartdate,

       

         .........................

       

         .........................

       

      From

      [$(vQvdPath)\WorkOrder.qvd]

       

      tmp_Work_Order:

      Load

           ServiceEventEquipmentId,

           ServiceEventStartdate

       

      resident Main_Work_Order;


      Coverage:

      Load

           CoverageId,

           EquipmentId,

           CoverageStartDate,

          CoverageEndDate,

          Coverage Type,

          WarrantyFlag,

          custpaidFlag

      from

      [$(vQvdPath)\Coverage.qvd]


      In the above scenario ServiceEventEquipmentId and EquipmentId are same, where as ServiceEventStartdate falls in between

      CoverageStartDate and CoverageEndDate. how to use Interval match to combine(join) those tmp_Work_Order and Coverage.

      Can anyone help me here.



      Cheers,

      Ganesh




        • Re: IntervalMatch help ?
          Stefan Wühl

          Maybe like this:

           

          Main_Work_Order:

          Load

              ServiceEventEquipmentId,

              ServiceEventStartdate,

           

            .........................

           

            .........................

           

          From

          [$(vQvdPath)\WorkOrder.qvd]

           

          Coverage:

          Load

              CoverageId,

              EquipmentId,

              CoverageStartDate,

              CoverageEndDate,

              Coverage Type,

              WarrantyFlag,

              custpaidFlag

          from

          [$(vQvdPath)\Coverage.qvd]


          INNER JOIN

          INTERVALMATCH (ServiceEventStartdate, ServiceEventEquipmentId)

          LOAD

               CoverageStartDate,

               CoverageEndDate,

               EquipmentId as ServiceEventEquipmentId

          RESIDENT Coverage;


          Look also into the HELP, IntervalMatch with extended syntax.

          • Re: IntervalMatch help ?
            Marco Wedel
            Intervalmatch (ServiceEventStartdate,ServiceEventEquipmentId)
            LOAD CoverageStartDate,
                      CoverageEndDate,
                      EquipmentId
            Resident Coverage;
            


            hope this helps


            regards


            Marco

              • Re: IntervalMatch help ?
                Marco Wedel

                Hi,

                 

                one example could be:

                 

                QlikCommunity_Thread_167481_Pic3.JPG

                 

                QlikCommunity_Thread_167481_Pic2.JPG

                 

                QlikCommunity_Thread_167481_Pic1.JPG

                 

                 

                 

                tmp_Work_Order:
                LOAD *,
                    AutoNumberHash128(ServiceEventEquipmentId,ServiceEventStartdate) as %WorkOrdID;
                LOAD Ceil(Rand()*10) as ServiceEventEquipmentId,
                    Date(Today()-Ceil(Rand()*30)) as ServiceEventStartdate
                AutoGenerate 50;
                
                Coverage:
                LOAD *,
                    AutoNumberHash128(EquipmentId,CoverageStartDate,CoverageEndDate) as %CoverID;
                LOAD *,
                    Date(CoverageStartDate+Ceil(Rand()*10)) as CoverageEndDate;
                LOAD Ceil(Rand()*10) as EquipmentId,
                    Date(Today()-Ceil(Rand()*30)) as CoverageStartDate
                AutoGenerate 50;
                
                tabLink:
                Intervalmatch (ServiceEventStartdate,ServiceEventEquipmentId) 
                LOAD CoverageStartDate, 
                    CoverageEndDate, 
                    EquipmentId as ServiceEventEquipmentId
                Resident Coverage;
                
                Left Join (tabLink)
                LOAD Distinct
                  AutoNumberHash128(ServiceEventEquipmentId,ServiceEventStartdate) as %WorkOrdID,
                  ServiceEventEquipmentId,
                  ServiceEventStartdate
                Resident tabLink;
                
                Left Join (tabLink)
                LOAD Distinct
                  AutoNumberHash128(ServiceEventEquipmentId,CoverageStartDate,CoverageEndDate) as %CoverID,
                  ServiceEventEquipmentId,
                  CoverageStartDate,
                  CoverageEndDate
                Resident tabLink;
                
                DROP Fields ServiceEventEquipmentId, CoverageStartDate, CoverageEndDate, ServiceEventStartdate From tabLink;
                

                 

                hope this helps (although initially having missed to rename the EquipmentId field )

                 

                regards

                 

                Marco