Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
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