Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

IntervalMatch based on TIMESTAMP field

I'm having problems making Intervalmatch to work for certain values of fields that are timestamps. I'm using the sample code below and it seems that the interval match gets lost on more than 4 decimal points for the value it is trying to match. So it doesn't like certain times like 7:00:00 AM because it goes out to like 9 decimal places. However, if you change the DateOpened to a time like 6 AM instead, it works like a charm. [End goal is to map how many staff are working for each hour of each day. Real Cases table uses employee number and start/stop date and time.]

LET vDateMin = Num(MakeDate(2009,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
 
TempCalendar:
LOAD  $(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

TimeStampCalendar:
LOAD Timestamp(DateNumber+NUM(MAKETIME(IterNo()-1, 0,0))) as TimestampField
Resident TempCalendar
while IterNo() <= 24;

Cases:
LOAD * INLINE [ CaseNumber, DateOpened, DateClosed
0001,'1/1/2009 07:00:01 AM', '1/1/2009 9:32:00 AM',   
0002,'1/2/2009 11:00:00 AM', '1/3/2009 6:32:00 PM'
]; 

LEFT JOIN ([TimeStampCalendar]) INTERVALMATCH (TimestampField)
LOAD     DateOpened, DateClosed
RESIDENT [Cases];  

LEFT JOIN ([TimeStampCalendar])
LOAD CaseNumber,
     DateOpened,
     DateClosed
RESIDENT [Cases];

Tags (1)
2 Replies
robert99
Valued Contributor II

Re: IntervalMatch based on TIMESTAMP field

I had the same problem

For some reason interval match (the time field) sometimes does a match and other times does not as shown below

It seems it does not work as it should for date and time values

EmployeeStartTimeCompleteTimeTime
00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:15:00
00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:20:00
00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:25:00
00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:30:00
00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:35:00
00206/07/2012 09:00:0006/07/2012 09:45:00-
00206/07/2012 09:45:0006/07/2012 10:24:00-
00206/07/2012 10:55:0506/07/2012 11:08:0506/07/12 11:00:00
00206/07/2012 10:55:0506/07/2012 11:08:0506/07/12 11:05:00
00206/07/2012 12:13:0106/07/2012 12:46:0306/07/12 12:15:00
00206/07/2012 12:13:0106/07/2012 12:46:0306/07/12 12:20:00
03806/07/2012 08:30:0006/07/2012 08:45:0006/07/12 08:40:00
03806/07/2012 08:45:0006/07/2012 09:00:0006/07/12 08:45:00
03806/07/2012 08:45:0006/07/2012 09:00:0006/07/12 08:50:00
03806/07/2012 08:45:0006/07/2012 09:00:0006/07/12 08:55:00
03806/07/2012 09:00:0006/07/2012 09:30:0006/07/12 09:00:00
03806/07/2012 09:00:0006/07/2012 09:30:0006/07/12 09:05:00
03806/07/2012 09:00:0006/07/2012 09:30:0006/07/12 09:10:00
robert99
Valued Contributor II

Re: IntervalMatch based on TIMESTAMP field

But this alternative approach seems to work

Thanks to this excellent post by Orka

http://community.qlik.com/thread/23161

I used a modified version of my example below at work and it seems to work as it should

ie I had to use where to limit the period etc

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

WorkTime:

Load * Inline [

EmployeeId, StartDate, EndDate

1,01/01/2009 06:00:00, 01/01/2009 06:25:00

1,01/01/2009 07:00:00, 01/01/2009 07:55:00

1,01/01/2009 07:45:00, 01/01/2009 07:50:00

2,01/01/2009 07:18:00, 01/01/2009 07:55:00

3,01/01/2009 07:25:00, 01/01/2009 07:35:00

3,01/01/2009 07:55:00, 01/01/2009 08:55:00

4,01/01/2009 07:59:00, 01/01/2009 08:55:00

];

left join (WorkTime)

load StartDate

,EndDate

,autonumber(StartDate & '-' & EndDate) as AutoNumWkTime

resident WorkTime;

WorkTimeByMinute:

load distinct

AutoNumWkTime,

StartDate + (IterNo()/24/60)   as Date

resident WorkTime

while StartDate + (IterNo()/24/60)   <= EndDate;

Dates:

load

Date,

Day(Date) as Day,

Hour (Date) as Hour,

Minute (Date) as Minute

Resident WorkTimeByMinute;

Community Browser