Extending IntervalMatch

[cb5981]

Hi All, 

I have a fact table which has an ID field, Approval Date, Approved, and Approval Delegated.

ID	Approval Date	Approved By	Approval Delegated
123456	14/03/2021	Mr. Brown	Yes

If the ID has an approval delegated as "Yes" it means that the ID was approved on behalf of someone. ie. Mr. Brown approved ID 122456 on behalf of another person.  To find this I have a table which gives the Delegator, Delegate and Start Date, End Date.  It shows who the Mr.Brown was delegating for at that time.

DELEGATION:

Delegator	Delegate	Start Date	End Date
Mr. Green	Mr. Brown	01/03/2021	01/04/2021
Mr. Red	Mr. Brown	01/05/2021	01/07/2021

i.e. ID 123456 was approved on 14/03/2021 and so was approved on behalf of Mr. Green. (Mr.Red is the delegator for a different time period)

I feel that intervalMatch should solve this but I am struggling to bring back the delegator name.

LOAD
ID,
Approval Date,
Approved By,
Approval Delegate

FROM FACT;

INTERVAL_MATCH:
IntervalMatch (Approval Date)
Load distinct [Start Date], [End Date] resident DELEGATION;

But this is only matching on the approval date, when I require it to match on the ID as well.
Maybe I am overlooking something so if anyone has an idea it would be very welCome.

Thanks

[rwunderlich · Accepted solution]

Interval match allows you to extend the match with key fields. See the doc here:

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

-Rob