calendar with hours

[#1]

Hi,

I would like to create a calendar with hours but unfortunately my script fails in the creation of the temp table.

I've tried a variety of formats but nothing seems to work. Any suggestions?

tmpMinMax:
LOAD
timestamp(min(THEDATE),'DD/MM/YYYY hh:mm:ss') as MIN,
timestamp(max(THEDATE),'DD/MM/YYYY hh:mm:ss') as MAX
RESIDENT FACTS;

LET vMinDate = num(peek( 'MIN', 0, 'tmpMinMax'));
LET vMaxDate = num(peek( 'MAX', 0, 'tmpMinMax'));


Temp:
LOAD
TIMESTAMP(($(vMinDate) + rowno() -1),'DD/MM/YYYY hh:mm:ss') as Date
AUTOGENERATE 1
WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

[#2]

Hi,

you can not use AUTOGENERATE together with WHILE!!!

Here are an example using WHILE and ITERNO():

Load Distinct
Item_number, //*** We need the "Item_Number" to get a complete calendar for each item
Date($(vMinDate) + Iterno() -1) as Transaction_date
Resident Suplier_1
While Iterno() <= $(vSteps)
;

Good luck!

Rainer

[#3]

Rainer,

thanks for the suggestion.

I've tried your suggestion, but run into a situation where millions of lines are created in the calendar but there is only 75 000 records in my fact table.

Any other suggestion or maybe I did something incorrect in using my max date for the ($vSteps)

ps. the code snippet does work if I use trunc(changedate) when I extract the data.

[#4 · Accepted solution]

Hi,

the amount of records depends on the number of ITEMS you have and not which kind of method (Autogenerate or WHILE) you use.

Without the additional field ITEMNUMBER i´m sure you will get the number of records you expect.

See the attached example for more information.

God luck!

Rainer

[#5]

Ah-ha!!

Thanks! works very well.