Intervalmatch end points

[#1]

Hi all,

Have a look at this code below. I expected row 5 of table t1 to have been associated with the band 1 interval (to 05:59:59) but it returns null. If I change the value in row 5 to 15:59:58 for example, it is fine. It is almost like the upper limit is not included in the range.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/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';

[bands]:
Load * inline [
band, HFrom, HTo
1, , 05:59:59
2, 06:00:00, 11:59:59
3, 12:00:00, 17:59:59
4, 18:00:00, 23:59:59
5, 24:00:00,
];

t1:
LOAD * INLINE [
F1, F2
2009-12-22-11.30.00.000000, 2009-12-23-12.45.00.000000
2009-12-22-10.00.00.000000, 2009-12-22-12.18.00.000000
2009-12-22-10.00.00.000000, 2009-12-22-16.00.00.000000
2009-12-22-10.00.00.000000, 2009-12-22-16.01.00.000000
2009-12-22-10.00.00.000000, 2009-12-22-15.59.59.000000
2009-12-22-10.00.00.000000, 2009-12-22-19.11.00.000000
2009-12-22-10.00.00.000000, 2009-12-22-23.55.00.000000
2009-12-23-08.22.00.000000, 2009-12-25-12.55.00.000000
];

t2:
load
timestamp#( F1,'YYYY-MM-DD-hh.mm.ss.ffffff' ) as stamp,
timestamp#( F2,'YYYY-MM-DD-hh.mm.ss.ffffff' ) as stamp1
resident t1;

t3:
load stamp,
stamp1,
stamp1 - stamp as diff,
interval(stamp1 - stamp) as diffx,
date(stamp) as stampdate,
time(stamp) as stamptime,
subfield('$(DayNames)', ';', weekday(stamp) + 1) as dayname,
date(stamp1) as stamp1date,
time(stamp1) as stamp1time,
subfield('$(DayNames)', ';', weekday(stamp1) + 1) as dayname1
resident t2;

left join (t3)
intervalmatch (diffx) load HFrom as XFrom, HTo as XTo resident bands;
left join
load band,
HFrom as XFrom,
HTo as XTo
resident bands;

drop table t1, t2;

drop field XFrom, XTo;

Any thoughts?

Regards,

Gordon

[johnw · Accepted solution]

I think you're getting hit by the use of binary floating point to represent decimal numbers. If you display your intervals as decimal numbers with 14 decimal places, you can see the problem with the records you want to match way down in the eleventh decimal place where you'd normally never see it:

diffx = 0.24998842593050
HTo = 0.24998842592593

The solution may be as simple as using 05:59:59.99999... with one more fractional digit in your ranges than your actual data will ever have. But I haven't experimented with how many fractional digits QlikView allows in an interval or timestamp, so perhaps that won't work.

If it doesn't, I believe that the only complete solution to the floating point binary problems is to deal only with integers, because integers can always be stored as exact rather than approximate values. In other words, if you want thousandths of a second accuracy, you have to load in your intervals as integers, specifically as the number of thousands of seconds. So your first interval is from 0 to 6 hours * 60 min/hour * 60 sec/min * 1000 - 1 = 21,599,999. Even then, I'm not convinced it will work right, because you're still reading in timestamps from your "database", and THEN multiplying by 3,600,000. If you know that your database is storing it in a decimal format rather than a binary format, and if you can multiply by 3,600,000 using the DBMS, that would probably guarantee correct results.

Of course that doesn't help you display the intervals in a useful way. I believe I'd make all these integers separate fields, "Integer HFrom", "Integer HTo", and so on. Once the interval match is complete, if you no longer need PERFECT accuracy, you could just drop them. Still, I'm guessing that just using more fractional digits on your ranges will work fine, and you won't need to go to all that trouble.

[rwunderlich]

One solution may be to round the diffx value to a second value:

interval(round(stamp1 - stamp, 1/86400)) as diffx,

-Rob

[johnw]

That appears to work for at least the sample data. Frankly, I'd probably do both to be certain - round off your interval to a second, and then use fractions of a second for your ranges.

[#5]

John/Rob,

I might have know it would be those pesky 0s and 1s at the root of the problem!

Appreciate both the help and the education.

Regards,

Gordon