Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raceberos
Partner - Contributor III
Partner - Contributor III

Calendar Loop

Hi guys im stuck in with my situation, where i need to create a  table base on start and end date 

IDNameStartDateEndDate
1Ben2019-01-152019-03-14
2Ren2019-03-122019-06-11

 

and the output must be like this

DateIDNameStartDateEndDate
2019-01-011Ben2019-01-152019-03-14
2019-02-011Ben2019-01-152019-03-14
2019-03-011Ben2019-01-012019-03-14
2019-03-012Ren2019-03-122019-06-11
2019-04-012Ren2019-03-122019-06-11
2019-05-012Ren2019-03-122019-06-11
2019-06-012Ren2019-03-122019-06-11

 

does anyone have an idea how to handle this kind in qlik sense

 

Thanks

Labels (2)
1 Solution

Accepted Solutions
PrashantSangle

try below code.

Test_data:
Load * Inline [
ID, Name, StartDate, EndDate
1, Ben, 2019-01-15, 2019-03-14
2, Ren, 2019-03-12, 2019-06-11
];


lood_date:
load distinct
ID as old_ID,
Name as old_Name,
StartDate as old_Start_Date,
EndDate as old_End_Date,
MonthStart(StartDate) as Month_start_date,
MonthStart(EndDate) as Month_end_date,
MonthStart(StartDate,IterNo()-1) as Date
resident Test_data
while AddMonths(StartDate,IterNo()-1) <= MonthStart(EndDate,1);
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 🙂

View solution in original post

3 Replies
Gysbert_Wassenaar

See this blog post for an example: https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944

Works in Qlik Sense too.

 


talk is cheap, supply exceeds demand
raceberos
Partner - Contributor III
Partner - Contributor III
Author

thanks bro
PrashantSangle

try below code.

Test_data:
Load * Inline [
ID, Name, StartDate, EndDate
1, Ben, 2019-01-15, 2019-03-14
2, Ren, 2019-03-12, 2019-06-11
];


lood_date:
load distinct
ID as old_ID,
Name as old_Name,
StartDate as old_Start_Date,
EndDate as old_End_Date,
MonthStart(StartDate) as Month_start_date,
MonthStart(EndDate) as Month_end_date,
MonthStart(StartDate,IterNo()-1) as Date
resident Test_data
while AddMonths(StartDate,IterNo()-1) <= MonthStart(EndDate,1);
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 🙂