IntervalMatch

[martynlloyd]

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;

[MK_QSL · Accepted solution]

Inner Join (ClaimFactstemp)

IntervalMatch ( [Defect Date], [Asset Number] )

Load [Contract Start], [Contract End], Asset Resident Contracts;

Join (ClaimFactstemp) Load * Resident Contracts;

Drop Table Contracts;

[martynlloyd]

Thanks Manish,

One more thing - I have some bad data: multiple contracts with same dates;

Asset Contract  Contract  Contract     Contract      Contract  Contract

          No.       Status    Status Date  Start Date    End Date  Code   

A123    193802     Z       2012/07/06  2010/03/31  2012/03/30     D2  

A123    193803     T       2010/04/26  2010/03/31  2012/03/30     F2  

********  End of report  ********                              

I need to pick one of these, first or last

Regards,

marty.

[MK_QSL]

Take the latest date .... this can be done while loading the tables initially

[MK_QSL]

have you intentionally select your answer as correct or that was a mistake?

[martynlloyd]

Mistake