How to create a reference date from two interval dates in fact table

[soka41]

Hello,

I want a same situation , in my fact table (Contrat) , i have two date (begin_date and end_date) which mean begin en end of contrat identify by Contrat_ID.

I want when a user choose one date (month , year or complete data) , application show a contrat that selected date is between begin_date and end_date contrat.

Contrat:
load * inline [
Contrat_ID ,Home_Contrat_ID, begin_dade , end_date
1,1,'01/01/2017','01/01/2018'
2,2,'01/01/2014','01/01/2016'
3,3,'01/11/2014','01/12/2014'
4,4,'01/11/2014','01/12/2014'
5,5,'02/09/2019',''
];
// '' means blanck space, when the contrat is active
// Contrat_ID = Home_Contrat_ID
Resident:
load * inline [
Resident_ID ,Contrat_ID , first_enter_dade , out_date
1,1,'01/01/2017','01/01/2018'
2,5,'02/09/2019',''
3,4,'01/01/2014','01/12/2014'
4,2,'01/01/2014','01/12/2016'
5,3,'01/11/2014','01/12/2014'
];

Home:
load * inline [
Home_ID ,Home_Contrat_ID, home_type
1,2,'T3'
2,4,'T1'
3,5,'T7'
4,3,'T3'
5,1,'1'
];

Datamodel

[jochem_zw · Accepted solution]

Have a look at IntervalMatch
https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

[robert99]

Or here's a video example of interval match

https://community.qlik.com/t5/Qlik-Design-Blog/Using-IntervalMatch/ba-p/1475510

[soka41]

thank to you for your video

[soka41]

thant to you , it work for me , good job 😎