topic Simple date question in QlikView

Simple date question

Tue, 26 May 2009 21:25:46 GMT

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.

Simple date question

Tue, 26 May 2009 21:29:23 GMT

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

Simple date question

Tue, 26 May 2009 21:46:27 GMT

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.

Simple date question

rbecher — Tue, 26 May 2009 21:53:34 GMT

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

Simple date question

Tue, 26 May 2009 22:02:32 GMT

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.

Simple date question

rbecher — Tue, 26 May 2009 22:11:12 GMT

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

Ralf

Simple date question

Tue, 26 May 2009 22:23:29 GMT

Hi Ralf,

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

Simple date question

rbecher — Tue, 26 May 2009 22:46:12 GMT

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