Autogenerating Time Intervals In Day

[paulferguson80]

Hi

I am having trouble getting the autogenerate function to work with time to create in intraday table.

I would like to generate dates, then times within each date so that i can build a fact table based on the time and day as a key, i have the date one working fine but the intraday times (15 minute intervals from 7 am to 10pm) i cant seem to get them to autogenerate and then allocate to each day in the previous autogenerate for the dates:

Any help would be greatly appreciated as i think its quite simple to do but just cant seem to get it to work.

Here is the code that i think should run and give me what im looking for but it only picks up the innital interval of 07:00 and doesnt autogenerate the full day.

LET varmindate = peek('schedDate',0,'raw');
LET varmaxdate = peek('schedDate',-1,'raw');


tempcalender:
load
date('$(varmindate)' + iterno()-1) as date
autogenerate 1 while '$(varmindate)' + iterno()-1 <= '$(varmaxdate)';

let varmintime = time('07:00:00','hh:mm');
let varmaxtime = time('22:00:00','hh:mm');

time_temp:
load
time('$(varmintime)' + iterno()-1) as int
autogenerate 1 while '$(varmintime)' + iterno()-1 <= '$(varmaxtime)';

In effect what im looking to do is create

Date , int

27/07/2009, 07:00

27/07/2009, 07:15

27/07/2009, 07:30

etc for each day in the first autogenerate statement

Many Thanks

Paul

[#2]

Hi Paul,

Replace :

time('$(varmintime)' + iterno()-1) as int

with

time(time('07:00:00','hh:mm') + time('00:'&15 * iterno() &':00','hh:mm')) as int

[#3 · Accepted solution]

Hi Paul,

This is probably not the best solution, but it shows the versatility of QlikView ito problem solving:

LET varmindate = peek(today());
//LET varmaxdate = peek('schedDate',-1,'raw');


tempcalender:
load
rowno() as row,
makedate(year(today()),month(today()),day(today()+rowno())) as date
autogenerate 12;// while '$(varmindate)' + iterno()-1 <= '$(varmaxdate)';


LEFT JOIN

LOAD * INLINE [
HOUR
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
];
LEFT JOIN
LOAD * INLINE [
Minute
15
30
45
00
];

[final]:
noconcatenate
LOAD *,
HOUR & ':' & Minute AS INTERVAL
RESIDENT tempcalender;

DROP TABLE tempcalender;

[paulferguson80]

Hi Abdul

Thank you for your response, it still doesnt seem to autogenerate the rest of the day?

I now get only the one interval of 07:15 as oppose to the original 07:00

Many Thanks

Paul

[paulferguson80]

Hi Wern

Thanks for this solution

This does give the desired effect and i have used the innital code to make it run between the dates that i need the table from and to so solves the problem that i have, so thanks for this it will certainly get me back on my way.

I am still interested in how to get the autogenerate to do this too as im sure it can be done, i will post it if i can get it to do it for future ref.

Many Thanks

Paul

[johnw]

Something like this, perhaps:

Calendar:
LOAD date(daystart($(varmindate)+(recno()-1)/96)) as Date
, time( frac($(varmindate)+(recno()-1)/96)) as Time
AUTOGENERATE ($(varmaxdate)-$(varmindate)+1)*96;

Edit: Looks like I forgot to tell QlikView to format these as date and time fields. I could do that in the properties, but I usually do it in the script. Corrected.

[rwunderlich]

Here's a variation on an example from the QV Cookbook:

LET vEndDate = num(today(1)); // End at today's date
LET vStartDate = $(vEndDate)-60; // Last 60 days
FOR t = MakeTime(7) to MakeTime(20) STEP MakeTime(0,15)
 DateMaster:
 LOAD
 date($(vStartDate) + IterNo()) as Date,
 time($(t)) as Time
 AUTOGENERATE 1
 WHILE $(vStartDate) + IterNo() <= $(vEndDate)
 ;
NEXT t

-Rob

[paulferguson80]

Hi Rob,

I know it was a while ago that you posted, i am just revisiting the process to try and refine and improve the process.

The code you posted looked to be the elegant replacement for the current inline loads and will work dynamically with the other code in the load script for the calendar that we have put together, the only problem is that when testing the code i found that the 15 minute incraments works excellently, however i need to interval match the time at seconds level.

So i have changed the STEP (0,15) to STEP(0,1)

This works up until 14:50:00 and then we loose a second?

The next time is 14:50:59

Im not clear as to why this happens at this time of the day but it seems to happen over all of the dates at the same time of day?

Can you shed any light on this problem?

I am running QV9 SR6 and have tried on both 32 and 64 bits thinking its service release problem, i have also run on SR1 to check this and the results dont change?

Many Thanks

Paul Ferguson

[rwunderlich]

Hi Paul,

The second is lost due to a limitation of precision when adding in the minute (1440/1) value. The MakeTime(0,1) is a decimal value that's close, but not exactly 1440/1. The imprecision eventually catches up. A more accurate solution is to generate the time values using discrete numbers in the loop like this:

LET vEndDate = num(today(1)); // End at today's date

LET vStartDate = $(vEndDate)-60; // Last 60 days

FOR vHour = 7 TO 20

FOR vMinute = 0 TO 59

DateMaster:
LOAD
date($(vStartDate) + IterNo()) as Date,
MakeTime($(vHour), $(vMinute)) as Time
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate);

NEXT vMinute

NEXT vHour

-Rob