topic Autogenerating Time Intervals In Day in QlikView

Autogenerating Time Intervals In Day

paulferguson80 — Thu, 16 Jul 2009 20:17:03 GMT

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

Autogenerating Time Intervals In Day

Thu, 16 Jul 2009 21:10:23 GMT

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

Autogenerating Time Intervals In Day

Thu, 16 Jul 2009 21:26:04 GMT

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;

Autogenerating Time Intervals In Day

paulferguson80 — Thu, 16 Jul 2009 21:42:18 GMT

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

Autogenerating Time Intervals In Day

paulferguson80 — Thu, 16 Jul 2009 21:48:24 GMT

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

Autogenerating Time Intervals In Day

johnw — Fri, 17 Jul 2009 03:22:08 GMT

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.

Autogenerating Time Intervals In Day

rwunderlich — Fri, 17 Jul 2009 06:09:50 GMT

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

Autogenerating Time Intervals In Day

paulferguson80 — Mon, 10 Jan 2011 12:13:15 GMT

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

Autogenerating Time Intervals In Day

rwunderlich — Fri, 14 Jan 2011 21:15:01 GMT

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