Help with Interval Match

[fcecconi]

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

[sunny_talwar]

What is your complete script?

[effinty2112 · Accepted solution]

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

[fcecconi]

Andrew

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