Create duplicated records between dates

[cheryl]

HI QlikSense Community,

I have a question: I have a original table highlighted in yellow. However, for each ChangeID (ChangeID is the unique identifier in the original table), I want to create duplicated records between the date interval (which is highlighted in blue; my example only shows the first date interval but same should applied to all the time interval). 

Does anybody know how to write script in Qliksense Load script to achieve this?

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

[maxgro · Accepted solution]

I hope the example can help you

// test data

X:
load * inline [
ChangeID, f2, dfrom
1, 1, 18/01/2022
1, 2, 20/01/2022
1, 3, 01/02/2022
2, 11, 28/03/2023
2, 12, 01/04/2023
2, 13, 05/04/2023
];

// add the date to (dto field)

X2:
load
*,
DATE(IF(peek(ChangeID)<>ChangeID, dfrom, peek(dfrom)-1)) as dto
Resident X
Order By ChangeID, dfrom desc;

DROP Table X;

//  create the records between date from (dfrom) and date to (dto)

X3:
LOAD
*,
DATE(dfrom + iterno()-1) as d
Resident X2
WHILE (dfrom + iterno()-1) <= dto;