Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Inner Join (ClaimFactstemp)
IntervalMatch ( [Defect Date], [Asset Number] )
Load [Contract Start], [Contract End], Asset Resident Contracts;
Join (ClaimFactstemp) Load * Resident Contracts;
Drop Table Contracts;
Inner Join (ClaimFactstemp)
IntervalMatch ( [Defect Date], [Asset Number] )
Load [Contract Start], [Contract End], Asset Resident Contracts;
Join (ClaimFactstemp) Load * Resident Contracts;
Drop Table Contracts;
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.
Take the latest date .... this can be done while loading the tables initially
have you intentionally select your answer as correct or that was a mistake?
Mistake