topic Populate every possible date between the open and start date range in QlikView

Populate every possible date between the open and start date range

Fri, 05 Feb 2010 01:03:28 GMT

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

Populate every possible date between the open and start date range

disqr_rm — Fri, 05 Feb 2010 04:15:40 GMT

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

Populate every possible date between the open and start date range

Fri, 05 Feb 2010 10:32:08 GMT

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

Populate every possible date between the open and start date range

vidyut — Fri, 05 Feb 2010 16:31:17 GMT

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);

Populate every possible date between the open and start date range

Sat, 06 Feb 2010 18:23:06 GMT

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

Populate every possible date between the open and start date range

lironbaram — Sun, 07 Feb 2010 16:34:23 GMT

attach is your file

with one more step , an interval match function

Populate every possible date between the open and start date range

Mon, 08 Feb 2010 09:44:17 GMT

Thank you very much guys for the input!

It works neat!

Regards,

Rishi

Populate every possible date between the open and start date range

Wed, 28 Apr 2010 08:24:45 GMT

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);