Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zagzebski
Contributor

Error in Interval Match

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

Tags (2)
6 Replies
Employee
Employee

Re: Error in Interval Match

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

zagzebski
Contributor

Re: Error in Interval Match

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:
RecordProvider IDService Date
1John1/1/2007
2John1/1/2009
3Steve1/1/2011
4Steve1/1/2014
PROVIDER_ALLOCATION TABLE:
ProviderBegin DateEnd DateAllocation_PCT
John1/1/200012/31/200750%
John1/1/20081/1/2013100%
Steve1/1/200012/31/200720%
Steve1/1/20081/1/201340%
RESULT:
RecordProvider IDService DateAllocation_PCT
1John1/1/200750%
2John1/1/2009100%
3Steve1/1/201120%
4Steve1/1/201440%

Steve

Employee
Employee

Re: Re: Error in Interval Match

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

Image1.png

HIC

Employee
Employee

Re: Re: Error in Interval Match

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

HIC

zagzebski
Contributor

Re: Error in Interval Match

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

Employee
Employee

Re: Error in Interval Match

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

Community Browser