Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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 (3)
1 Solution

Accepted Solutions
Highlighted

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.

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Partner
Partner

thanks bro
Highlighted

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.

View solution in original post