Discussion Board for collaboration on QlikView Scripting.
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)LOADAsset,ContractID,[Contract Status],[Contract Start],[Contract End],SourceResident Contracts;
DROP Table Contracts;
NoConcatenateClaimFacts:LOAD *Resident ClaimFactstempWhere [Defect Date] >= [Contract Start] AND [Defect Date] <= [Contract End];
DROP Table ClaimFactstemp;
Go to 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;
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
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?