topic calendar with hours in QlikView

calendar with hours

Fri, 14 Jan 2011 11:34:44 GMT

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

calendar with hours

Fri, 14 Jan 2011 12:20:19 GMT

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

calendar with hours

Fri, 14 Jan 2011 13:51:37 GMT

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.

calendar with hours

Fri, 14 Jan 2011 14:23:35 GMT

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

calendar with hours

Fri, 14 Jan 2011 15:30:55 GMT

Ah-ha!!

Thanks! works very well.