Transform from/to dates in a monthly table

[qlikconsultant]

Hi

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?

[Dementor · Accepted solution]

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;

[Aditya_Chitale]

Can you share sample data set and expected output ?

Regards,
Aditya

[qlikconsultant]

It's in my post.