Populate every possible date between the open and start date range

[#1]

Hi all,

I have a dataset with a task_number with a start and end date. I would like to display this in a chart with the x-axis being the date. My purpose is to show the open and closed task over time.

The data looks like:

Task Number Task Created Date Task End Date Task Status Open Tasks Max Date
21412826 26.8.2009 Open 11.1.2010
21600258 15.9.2008 17.9.2008 Closed

If a Task is Closed it has an End Date if not it has an Open Tasks Max Date. So the open task with TaskNumber 21412826 will be recorded with one record from 26.8.2009 to the 'Open Tasks Max Date' field which is 11.1.2010. The Closed Task Number 21600258 will be recorded on the 15.9.2008, 16.8.2008 and 17.8.2008 (the Task End Date).How can I achieve this?

Regards,

Rishi

[disqr_rm]

Have a look here: http://community.qlik.com/forums/t/25279.aspx

Similar kind of logic should work for you. Just a quick thought.

Rakesh

[#3]

Hi Rakesh,

I already set the max and min date. I would like to have in one table every date between the start and end date.

Rishi

[vidyut]

Hi Rishi,

Rakesh pointed you to right place, the code is available there to generate the dates between a min and max date. That should suffice, isn't it. Check the code starting:

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATETempDate AS EstProjFin,

1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

[#5]

Hi Vidyut and Rakesh,

Thanks for the direction. I have now created two tables, one with the tasks and start and end dates and the other the Calendar with all the possible values from the Tasks table.

How do I populate the dates between the start and end date with the values from the Calendar for every Tasknumber recorded?

I am sorry being kind of a newbie in Qlikview...

Regards,

Rishi

[lironbaram · Accepted solution]

hi

attach is your file

with one more step , an interval match function

[#7]

Thank you very much guys for the input!

It works neat!

Regards,

Rishi

[#8]

Hi,

Use this,

LET vDateMin = Num(AddYears(Today(),-1));
LET vDateMax = Num(Today());
LET NoOfDays = $(vDateMax) - $(vDateMin);

TempCalendar:
LOAD
Num($(vDateMax) - recno() + 1) as CalendarDateNum,
date($(vDateMax) - recno() + 1) as CalendarDate
AUTOGENERATE
$(NoOfDays);