topic Re: Error in Interval Match in QlikView

Error in Interval Match

zagzebski — Mon, 25 Nov 2013 20:09:10 GMT

Looking for what I am doing wrong with interval match below.

I have two feilds in the F_MEDICAL_CLAIM_DETAIL load called "Product-Entity" and
"OwnerClaimID" I was hoping to join to those two fields to the right side of the join.

Then I have a field "Incurred YearMonth" in the which I need to fall between EFFECTIVE_YEARMTH and EXPIRATION_YEARMTH.

However it doesnt seem to be joining accurately. Any ideas?

F_MEDICAL_CLAIM_DETAIL:

Load *,
Resident F_MEDICAL_CLAIM_DETAIL_PRE;

left Join (F_MEDICAL_CLAIM_DETAIL)
IntervalMatch([Incurred YearMonth])

LOAD
EFFECTIVE_YEARMTH,
EXPIRATION_YEARMTH,
[Product-Entity],
OwnerClaimID,
OWNERSHIP_PCT,
OWNER_DEFINITION,
UPDATE_DATE_D_OWNER_CLAIMS
FROM$(vInputFile11)(qvd);

Thanks,

Steve

Re: Error in Interval Match

hic — Mon, 25 Nov 2013 21:10:35 GMT

The Load that follows an IntervalMatch prefix should only have two fields: Lower bound and upper bound. And the field in the brackets should be the number that you want to match against the interval. You use F_MEDICAL_CLAIM_DETAIL when you should use [Incurred YearMonth].

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

HIC

Re: Error in Interval Match

zagzebski — Mon, 25 Nov 2013 21:44:38 GMT

Thanks for the response. Your blog was very informative.

However what happens in the case (like mine) where you need to use the interval match to get fit the date into ranges from a table BUT ALSO need to join on an additional field to join on.

Like in the simplied tables below I need to join on Provider ID and I need to see where service date falls between the Begin Date and End Date..

PROVIDER_FACT_TABLE:
Record	Provider ID	Service Date
1	John	1/1/2007
2	John	1/1/2009
3	Steve	1/1/2011
4	Steve	1/1/2014
PROVIDER_ALLOCATION TABLE:
Provider	Begin Date	End Date	Allocation_PCT
John	1/1/2000	12/31/2007	50%
John	1/1/2008	1/1/2013	100%
Steve	1/1/2000	12/31/2007	20%
Steve	1/1/2008	1/1/2013	40%
RESULT:
Record	Provider ID	Service Date	Allocation_PCT
1	John	1/1/2007	50%
2	John	1/1/2009	100%
3	Steve	1/1/2011	20%
4	Steve	1/1/2014	40%

Steve

Re: Re: Error in Interval Match

hic — Tue, 26 Nov 2013 08:34:06 GMT

I am not sure I understand why you want to join. I would instead go for the following data model:

HIC

Re: Re: Error in Interval Match

hic — Tue, 26 Nov 2013 09:37:30 GMT

... but should you want to join, you can do that too. I think the result is correct.

HIC

Re: Error in Interval Match

zagzebski — Tue, 26 Nov 2013 16:43:37 GMT

Thanks for the help - I am learning!

I guess maybe I didn't totally explain myself. My goals was to just link on the Provider ID AND use interval match to find the proper record that the service date fit into , THEN take the field(s) I need like "Allocation_PCT" and make it part of the PROVIDER_FACT_TABLE. My intention is not to create or keep any additional tables just to join a new field(s) into the PROVIDER_FACT_TABLE.

Does that make sense?

Steve

Re: Error in Interval Match

hic — Tue, 26 Nov 2013 19:26:33 GMT

If you use the three-table solution (in the script) I suggested above, you will be able to create exactly the table you want when you create the application UI. That's one way to do do it.

Another way is to join all three tables in the script:

Facts: Load ... from Facts ;

Allocation: Load ... from Allocation ;

Left Join (Facts) IntervalMatch (...) Load ... Resident Allocation;

Left Join (Facts) Load ... Resident Allocation;

Drop Table Allocation;

Drop Fields <List of fields you don't want>;

But this is somewhat risky: If a date belongs to two intervals, e.g. a date is the end of one interval and the beginning of another, you will get a fact table with too many records. But the three-table solution is safe...

HIC