Simple date question

[#1]

I am trying to create a calender and am hiting trouble right at the offset, was hoping for some help !

I have the following load,

WeekDates:
LOAD Year,
Period,
Week,
[Start Date],
[End Date],
FROM $(cFileName) (qvd);

I would like to create a record for every date between the start and end date in the table, I have tried the autogenerate comand but cant seem to get it to work. I cold change the file that I am reading to have all the information but this seems like something that should be simple to do in QlikView.

[#2]

Here is an example of working autogenerate code used with dates.

sub LoadCalendar

// Calendar Section - Begin
let StartDate =num(date('2008-01-01', 'MM/DD/YYYY'));
let EndDate=num(date(today()-1, 'MM/DD/YYYY'));

temp:
LOAD
timestamp#(date($(StartDate)+(iterno()-1), 'YYYY-MM-DD') & ' 00:00:00', 'YYYY-MM-DD hh:mm:ss') AS CheckPointStart
autogenerate 1
while date($(StartDate)+(iterno()-1))<=date($(EndDate));

Calendar:
LOAD distinct CheckPointStart,
date(CheckPointStart, 'MM/DD/YYYY') as DayDate,
Week(CheckPointStart) as Week,
Month(CheckPointStart) as Month,
Year(CheckPointStart) as Year,
Day(CheckPointStart) as Day
Resident temp;

DROP TABLE temp;

// Calendar Section - End

end sub

[#3]

Thanks for your prompt reply,

Unfortunately I don't think I can do as you suggested because out week/year/day numbers don't correspond to the ones used by QlikView (they done offset by exact months). The file I was loading in was outputted directly from out WMS, to make things even more confusing our year start can differ by a few days each year and the number of weeks changes every 4 years ( confusing I know). I was hoping for a simple way to create a record for each day containing date and our week ,year and period.

[rbecher]

Are you talking about a business year instead of calendar year?

[#5]

Yes, we hold the information as is shown in the load command and I was trying to be able to select date/week or year for reporting in QlikView. Like I said I could change the export to output a record for each day but thought it would be a simple exercise to do this in QlikView.

[rbecher]

Then use a calendar with all business days as second table and join with it.

Ralf

[#7]

Hi Ralf,

Sorry Ralf am new to this and dont fully understand what you are saying.

[rbecher]

1. create a Calendar like this from Eugene: sub LoadCalendar

2. the join:

WeekDates:
LOAD Year,
Period,
Week,
[Start Date],
[End Date],
FROM $(cFileName) (qvd);

JOIN (WeekDays)
LOAD Week, DayDate RESIDENT Calendar;

The join would go on column Week (same col name).

Ralf