Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Creator

## Splitting a row into multiple rows according to a date range

Hello,

I have an original table

cod, quantity, date_start, date_end

DE2520, 10 , 01-01-2020, 31-10-2020

and this is the final table I need to have. generating row depending on how much month there is between start date and end date

cod, quantity, month, year

DE2520, 1 , 1,2020,

DE2520, 1 , 2,2020,

DE2520, 1 , 3,2020,

DE2520, 1, 4,2020,

DE2520, 1 , 5,2020,

DE2520, 1 , 6,2020,

DE2520, 1, 7,2020,

DE2520,1, 8,2020,

DE2520,1, 9,2020,

DE2520,1 , 10,2020

thanks

Labels (1)
• ### Script

1 Solution

Accepted Solutions

FYI,

Test:
((year(new_date_end)+month(new_date_end))-(year(date_start)+month(date_start)))+1 as diff_in_mnths;
date(MonthStart(date_end)) as new_date_end;
quantity,
Date(Date#(date_start,'DD-MM-YYYY')) as date_start,
Date(Date#(date_end,'DD-MM-YYYY')) as date_end
Inline [
cod, quantity, date_start, date_end
DE2521, 9 , 01-01-2021, 30-09-2021
DE2520, 10 , 01-01-2020, 31-10-2020
];

NoConcatenate
Final:
Year(date) as year,
num(Month(date)) as month;
quantity / diff_in_mnths as new_qty,
Resident Test
While AddMonths(date_start,IterNo() - 1) < date_end
;

drop table Test;

Exit Script;

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
2 Replies

FYI,

Test:
((year(new_date_end)+month(new_date_end))-(year(date_start)+month(date_start)))+1 as diff_in_mnths;
date(MonthStart(date_end)) as new_date_end;
quantity,
Date(Date#(date_start,'DD-MM-YYYY')) as date_start,
Date(Date#(date_end,'DD-MM-YYYY')) as date_end
Inline [
cod, quantity, date_start, date_end
DE2521, 9 , 01-01-2021, 30-09-2021
DE2520, 10 , 01-01-2020, 31-10-2020
];

NoConcatenate
Final:
Year(date) as year,
num(Month(date)) as month;
quantity / diff_in_mnths as new_qty,
Resident Test
While AddMonths(date_start,IterNo() - 1) < date_end
;

drop table Test;

Exit Script;

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Creator
Author

Thanks Prashant great.

This is what I needed

Enrico