5 Replies Latest reply: Jul 22, 2014 11:38 AM by Martyn Lloyd RSS

    IntervalMatch

    Martyn Lloyd

      I have 2 tables, a Contract and a Claim. The only way to match a Claim to a Contract is via the Asset and Start/End dates

       

      the following script works ok, but I was wondering how to achieve the same using IntervalMatch?

       

      Contracts:
      LOAD CountContracts,
           Source,
           ContractID,
           Asset,
           [Contract Status],
           [Contract Status Date],
           Date([Contract Start]) as [Contract Start],
           Date([Contract End]) as [Contract End]
      FROM
      [..\SourceData\QVD\KPD_Contracts.qvd]
      (qvd);


      ClaimFactstemp:
      LOAD RecID,
           1 as CountClaims,
           [Dealer Number],
           [Claim Number],
           [Claim Type],
           [Asset Number] as Asset,
           [Date Paid],
           Date([Defect Date]) as [Defect Date],
           Year([Defect Date]) as DefectYear, //KPIYear,
           Month([Defect Date]) as DefectMonth //KPIMonth,
      FROM
      [$(vQVDPath)\KPZ_Claims_H.qvd](qvd)
      ;

       

      Left Join (ClaimFactstemp)
      LOAD
      Asset
      ,ContractID
      ,[Contract Status]
      ,[Contract Start]
      ,[Contract End]
      ,Source
      Resident Contracts
      ;

      DROP Table Contracts;

       

      NoConcatenate
      ClaimFacts:
      LOAD *
      Resident ClaimFactstemp
      Where [Defect Date] >= [Contract Start] AND [Defect Date] <= [Contract End]
      ;

      DROP Table ClaimFactstemp;