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

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

soka41 — Sat, 16 Nov 2024 19:03:58 GMT

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

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

jochem_zw — Sat, 22 Feb 2020 16:17:01 GMT

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

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

robert99 — Sat, 22 Feb 2020 19:21:27 GMT

Or here's a video example of interval match

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

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

soka41 — Mon, 24 Feb 2020 16:36:58 GMT

thank to you for your video

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

soka41 — Mon, 24 Feb 2020 16:37:57 GMT

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