topic Re: Help with Interval Match in QlikView

Help with Interval Match

fcecconi — Wed, 05 Oct 2016 19:50:58 GMT

Hello

I am trying to match on a key and a date, but I can't seem to get the right logic. I need to add the start and end date of a program for an individual based on a service date. I also need to have the matched data joined to the fact table.

Billings:

load

Name,

Service,

date_of_service,

PATID & E_N as P1_KEY

Resident _Billings;

Program:

Load

PATID & E_N as P1_KEY,

Program,

Start_Date,

End_Date,

Close_Date,

This is what I need to end up with

Fact:

Date of Service	Name	Service	Program	Start Date	End Date	Close Date
08/30/2016	Steve	112	D	08/29/2016	09/01/2016	09/04/2016
08/31/2016	Steve	112	D	08/29/2016	09/01/2016	09/04/2016
08/31/2016	Mary	113	R	08/31/2016	09/05/2016	09/05/2016
09/01/2016	Steve	113	R	09/01/2016	09/04/4016	09/04/2016
09/01/2016	Mary	113	R	08/31/2016	09/05/2016	09/05/2016
...
09/03/2016	Steve	113	R	09/01/2016	09/04/2016	09/04/2016
09/03/2016	Mary	113	R	08/31/2016	09/05/2016	09/05/2016
09/04/2016	Mary	113	R	08/31/2016	09/05/2016	09/05/2016

Re: Help with Interval Match

sunny_talwar — Wed, 05 Oct 2016 19:58:48 GMT

What is your complete script?

Re: Help with Interval Match

effinty2112 — Wed, 05 Oct 2016 21:55:01 GMT

Hi Frank,

Check out the IntervalMatch function with extended syntax in the Qlikview help file. In your example your script could have a line like:

IntervalMatch(date_of_service,P1_KEY) LOAD Start_Date, End_Date, P1_KEY Resident Program;

This will create a synthetic key in your data model and it's perfectly okay to leave it in the model in this situation. Now Qlikview will be able to associate each date_of_service for a particular billing to the interval for the correct program for that billing given in your table "Program".

Good luck

Andrew

Re: Help with Interval Match

fcecconi — Thu, 06 Oct 2016 18:33:28 GMT

Andrew

thanks. I also had to add in a left join to complete the code.