Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Assume the following dataset
Set NullInterpret = '';
Let vCurrentDate = Num(Date(Today()));
Data:
Load * Inline [
Id,StartDate,EndDate
1, 01-01-2019,
2, 25-01-2020, 27-08-2020
3, 01-01-2020,
];
What I try to achieve is creating a new "monthly" record for each record untill an EndDate is hit. So ID 2 will have 8 repeats, while ID 1 and 3 continue till vCurrentDate.
The final result will look like this:
ID | YearCounter | MonthCounter | StartDate | EndDate |
1 | 1 | 1 | 01-01-2019 | |
1 | 1 | 2 | ||
1 | 1 | 3 | ||
1 | ... | ... | ||
1 | 2 | 20 | ||
2 | 2 | 13 | 25-01-2020 | |
2 | 2 | 14 | ||
2 | 2 | 15 | ||
2 | 2 | 16 | ||
2 | 2 | 17 | ||
2 | 2 | 18 | ||
2 | 2 | 19 | ||
2 | 2 | 20 | 27-08-2020 | |
3 | 2 | 13 | 01-01-2020 | |
3 | ... | ... |
What you need is an Intervalmatch function.
Check details here. https://community.qlik.com/t5/Qlik-Design-Blog/Using-IntervalMatch/ba-p/1475510