Help with a load script

[#1]

Hi guys,

I am having a small issue with a load script.

Script is as follows:

Date_Table:
LOAD DATE,
date(date#(DATE_YEAR_MONTH,'MMMYYYY'),'MMM-YYYY') as [DATE Month-Year],

DATE_YEAR

FROM

Location ......

where DATE_YEAR = '2009' OR DATE_YEAR = '2010' or DATE_YEAR = '2011'';

Now, my script works fine, but it returns all the months for 2011 although I have no data for them yet.

I edited the script to read as follows:

where DATE_YEAR = '2009' OR DATE_YEAR = '2010' or date(date#(DATE_YEAR_MONTH,'MMMYYYY'),'MMM-YYYY') = 'Jan-2011`';

That works fine, but it means that when we get to use February data, I will have to add Feb-2011 to the WHERE clause and will need to do this for every new month which I do not want to do.

In addition, I cannot use today() function in conjunction with addmonths() because the way our app data becomes available does not conform with regular time and dates.

Is there any other way, I can solve this issue with having to add a new month everytime I receive new data.

Any help with this would be greatly appreciated.

Thank you

[llauses243]

Hi,

I don't use clause WHERE for calendar table

For data table i use artificio (see image attached) for omitt NULL values

Good luck, Luis.

[#3]

Hi Ilauses,

The major problem I have is that I have some expressions which use max(Date) and addMonths(). If I load everything including the full year 2011, my max date becomes Dec-2011 and not Jan-2011, even though I have no data at the moment.

I need to make the modification in the load script to restrict what comes in the first place.

[johnw · Accepted solution]

Find the maximum date for which you have data. Load where DATE <= that date.

For that matter, if all you're doing is loading date information, is there a reason to load from a source instead of just autogenerating a calendar?