topic Re: IntervalMatch in QlikView

IntervalMatch

martynlloyd — Tue, 22 Jul 2014 14:33:17 GMT

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;

Re: IntervalMatch

MK_QSL — Tue, 22 Jul 2014 14:39:09 GMT

Inner Join (ClaimFactstemp)

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

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

Join (ClaimFactstemp) Load * Resident Contracts;

Drop Table Contracts;

Re: IntervalMatch

martynlloyd — Tue, 22 Jul 2014 15:10:03 GMT

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.

Re: IntervalMatch

MK_QSL — Tue, 22 Jul 2014 15:12:55 GMT

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

Re: IntervalMatch

MK_QSL — Tue, 22 Jul 2014 15:34:09 GMT

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

Re: IntervalMatch

martynlloyd — Tue, 22 Jul 2014 15:38:39 GMT

Mistake