This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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;
Inner Join (ClaimFactstemp)
IntervalMatch ( [Defect Date], [Asset Number] )
Load [Contract Start], [Contract End], Asset Resident Contracts;
Join (ClaimFactstemp) Load * Resident Contracts;
Drop Table Contracts;
View solution in original post
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?