topic Re: IntervalMatch based on TIMESTAMP field in QlikView

IntervalMatch based on TIMESTAMP field

Fri, 08 Jul 2011 19:38:43 GMT

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

Re: IntervalMatch based on TIMESTAMP field

robert99 — Fri, 13 Jul 2012 10:17:10 GMT

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

Employee	StartTime	CompleteTime	Time
002	06/07/2012 08:12:28	06/07/2012 08:35:28	06/07/12 08:15:00
002	06/07/2012 08:12:28	06/07/2012 08:35:28	06/07/12 08:20:00
002	06/07/2012 08:12:28	06/07/2012 08:35:28	06/07/12 08:25:00
002	06/07/2012 08:12:28	06/07/2012 08:35:28	06/07/12 08:30:00
002	06/07/2012 08:12:28	06/07/2012 08:35:28	06/07/12 08:35:00
002	06/07/2012 09:00:00	06/07/2012 09:45:00	-
002	06/07/2012 09:45:00	06/07/2012 10:24:00	-
002	06/07/2012 10:55:05	06/07/2012 11:08:05	06/07/12 11:00:00
002	06/07/2012 10:55:05	06/07/2012 11:08:05	06/07/12 11:05:00
002	06/07/2012 12:13:01	06/07/2012 12:46:03	06/07/12 12:15:00
002	06/07/2012 12:13:01	06/07/2012 12:46:03	06/07/12 12:20:00
038	06/07/2012 08:30:00	06/07/2012 08:45:00	06/07/12 08:40:00
038	06/07/2012 08:45:00	06/07/2012 09:00:00	06/07/12 08:45:00
038	06/07/2012 08:45:00	06/07/2012 09:00:00	06/07/12 08:50:00
038	06/07/2012 08:45:00	06/07/2012 09:00:00	06/07/12 08:55:00
038	06/07/2012 09:00:00	06/07/2012 09:30:00	06/07/12 09:00:00
038	06/07/2012 09:00:00	06/07/2012 09:30:00	06/07/12 09:05:00
038	06/07/2012 09:00:00	06/07/2012 09:30:00	06/07/12 09:10:00

Re: IntervalMatch based on TIMESTAMP field

robert99 — Fri, 13 Jul 2012 10:23:48 GMT

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;