Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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 |
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;