Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
cheryl
Contributor III
Contributor III

Create duplicated records between dates

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?

cheryl_0-1686069265308.png

 

Labels (3)
1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

2 Replies
maxgro
MVP
MVP

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;