topic Re: Transform from/to dates in a monthly table in App Development

Transform from/to dates in a monthly table

qlikconsultant — Fri, 16 May 2025 10:26:49 GMT

i have the following table

Personalnumber Status from date to date

1, NEK, 01.12.2024, 28.02.2025

I have to transform in this format

Personalnumber Status Year Month

1, NEK, 2024, 12

1, NEK, 2025, 1

1, NEK, 2025, 2

Any suggestion what the best way is to transform this?

Re: Transform from/to dates in a monthly table

Dementor — Fri, 16 May 2025 11:23:07 GMT

Try while loop to create multiple items using conditions with IterNo()

Table:
LOAD
*
INLINE [
Personalnumber, Status, StartDate, EndDate
1, NEK, 01.12.2024, 28.02.2025
];

Table_v2:
load Personalnumber, Status,
Year(monthstart(date#(StartDate,'DD.MM.YYYY'),IterNo()-1)) as Year,
month(monthstart(date#(StartDate,'DD.MM.YYYY'),IterNo()-1)) as Month
Resident Table
while monthstart(date#(StartDate,'DD.MM.YYYY'),IterNo()-1) <=MonthStart(date#(EndDate,'DD.MM.YYYY'));

drop table Table;

Exit Script;

Re: Transform from/to dates in a monthly table

Aditya_Chitale — Fri, 16 May 2025 11:38:09 GMT

Can you share sample data set and expected output ?

Regards,
Aditya

Re: Transform from/to dates in a monthly table

qlikconsultant — Mon, 26 May 2025 12:49:42 GMT

It's in my post.