topic Re: time difference calculation issue in QlikView

time difference calculation issue

Wed, 06 May 2015 04:11:31 GMT

Dear All,

i am calculating a time difference between two times. i have found in some cases time difference is about 20 ms only but in number format its contain different demension and in time format it shows 20ms only.

below is the sample data, i have also tried using interval() and other time() timestamp() etc function but getting same output

even i have tried by converting ordtimestamp by 3 digit point also.

tbl_orders:

LOAD * INLINE [

ORDKEY, ORDTIMESTAMP, TRADETIMESTAMP

2014-03-05/512361/23000030323397, 2014-03-05 15:02:03.208001, 2014-03-05 15:02:03.228

2014-03-05/512417/19000137426159, 2014-03-05 14:47:20.619389, 2014-03-05 14:47:20.639

2014-03-05/500171/15000108205982, 2014-03-05 15:01:13.441897, 2014-03-05 15:01:13.461

2014-03-05/500285/14000004008996, 2014-03-05 09:30:56.345519, 2014-03-05 09:30:56.365

2014-03-05/500285/20000002299288, 2014-03-05 12:45:17.366308, 2014-03-05 12:45:17.386

];

tbl_new:

load * , TRADETIMESTAMP-ORDTIMESTAMP as diff Resident tbl_orders;

DROP Table tbl_orders;

Re: time difference calculation issue

Wed, 06 May 2015 05:08:47 GMT

Can anyone help me ??

Re: time difference calculation issue

PrashantSangle — Wed, 06 May 2015 05:16:56 GMT

Hi,

Try below in script,

tbl_orders:

LOAD * INLINE [

ORDKEY, ORDTIMESTAMP, TRADETIMESTAMP

2014-03-05/512361/23000030323397, 2014-03-05 15:02:03.208001, 2014-03-05 15:02:03.228

2014-03-05/512417/19000137426159, 2014-03-05 14:47:20.619389, 2014-03-05 14:47:20.639

2014-03-05/500171/15000108205982, 2014-03-05 15:01:13.441897, 2014-03-05 15:01:13.461

2014-03-05/500285/14000004008996, 2014-03-05 09:30:56.345519, 2014-03-05 09:30:56.365

2014-03-05/500285/20000002299288, 2014-03-05 12:45:17.366308, 2014-03-05 12:45:17.386

];

tbl_new:

load * , Interval(timestamp#(TRADETIMESTAMP,'YYYY-MM-DD hh:mm:ss.ffffff')-Timestamp#(ORDTIMESTAMP,'YYYY-MM-DD hh:mm:ss.fff'),'hh:mm:ss ffffff') as diff Resident tbl_orders;

drop Table tbl_orders;

Regards

Re: time difference calculation issue

tresB — Wed, 06 May 2015 05:22:32 GMT

In qv timestamp and number are different. Numeric 1 is equivalent to 1 day. Hence:

1 hour = 1/24

1 minute = 1/24/60

1 second = 1/24/60/60

1 milli second (ms)= 1/24/60/60/1000

Hope this helps you comprehend.

Re: time difference calculation issue

Wed, 06 May 2015 05:23:38 GMT

Hi Max,

i tried again that what u said,No difference its same.. i have tried all combination

Re: time difference calculation issue

PrashantSangle — Wed, 06 May 2015 05:26:39 GMT

Hi,

What is your expected output??

Regards

Re: time difference calculation issue

Wed, 06 May 2015 05:31:02 GMT

i got your point. but when there is difference of 20ms only between two timestamp when why is showing more than one record in list box. list box didnot repeat duplicate value.

if i convert the result into string its working and showing one value in listbox. but i cannot convert this to string because on this result i have to again put a intervalMatch().

if you look to diff listbox it show u two value for same 20ms.

Re: time difference calculation issue

Wed, 06 May 2015 05:35:24 GMT

if i convert into timestamp then it show 00:00:00.020 which is right i want that only. but if you the concept of listbox, it show unique values only. but in this case is showing duplicate value that means there is difference between these two.

and also when i am applying intervalMatch() on this then few record getting into this criteria and few not..

Re: time difference calculation issue

PrashantSangle — Wed, 06 May 2015 05:35:27 GMT

Hi,

Ok, Good point.

Take diff as list box

then go to property-> Number-> Override Document Setting-> Fixed to 12 Decimal

You see actual differnece between those 2 values.

Regards

Re: time difference calculation issue

tresB — Wed, 06 May 2015 05:35:53 GMT

Because it is not exactly 20 ms. If you increase the precision may be till nano seconds(ns) or more, the difference could be visible.

Re: time difference calculation issue

Wed, 06 May 2015 05:38:08 GMT

But when i convert both timestamp as below then also getting the same.

LOAD * INLINE [

ORDKEY, ORDTIMESTAMP, TRADETIMESTAMP

2014-03-05/512361/23000030323397, 2014-03-05 15:02:03.208, 2014-03-05 15:02:03.228

2014-03-05/512417/19000137426159, 2014-03-05 14:47:20.619, 2014-03-05 14:47:20.639

2014-03-05/500171/15000108205982, 2014-03-05 15:01:13.441, 2014-03-05 15:01:13.461

2014-03-05/500285/14000004008996, 2014-03-05 09:30:56.345, 2014-03-05 09:30:56.365

2014-03-05/500285/20000002299288, 2014-03-05 12:45:17.366, 2014-03-05 12:45:17.386

];

Re: time difference calculation issue

Wed, 06 May 2015 05:42:51 GMT

What should i do then, because on this result i have to apply below intervalMatch().

and after applying same i am found some of the records not falling in any criteria

TIMECAT:

LOAD * Inline [

FROM_TIME, TO_TIME, TIME_CAT

'00:00:00.000', '00:00:00.020', '10-20ms'

'00:00:00.021', '00:00:00.050', '21-50ms'

'00:00:00.051', '00:00:00.100', '51-100ms'

'00:00:00.101', '00:00:00.200', '101-200ms'

'00:00:00.201', '00:00:00.500', '201-500ms'

'00:00:00.501', '00:00:01.000', '501-1000ms'

'00:00:01.001', '00:00:02.000', '1-2sec'

'00:00:02.001', '00:00:05.000', '2-5sec'

'00:00:05.001', '00:00:10.000', '5-10sec'

'00:00:10.001', '00:00:20.000', '10-20sec'

'00:00:20.001', '00:00:50.000', '20-50sec'

'00:00:50.001', '00:01:00.000', '50-60sec'

'00:01:00.001', '16:00:00.000', '1Min+'

];

Re: time difference calculation issue

tresB — Wed, 06 May 2015 05:46:00 GMT

It's because of binary floating point representation. I suggest to read this blog: Rounding Errors

Re: time difference calculation issue

stevedark — Wed, 06 May 2015 06:12:38 GMT

What you are seeing here Jitendra is the effect of date/time values being stored as floating point values. Even though both values are represented the same to 20ms behind the scenes they are slightly different, at an even lower level of granularity.

If you add a round statement into the load this should make things better, for instance:

date(round(timestamp#(TRADETIMESTAMP,'YYYY-MM-DD hh:mm:ss.ffffff'), 0.00001), 'YYYY-MM-DD hh:mm:ss.ffffff')

Or you could just take a more limited number of characters from the string:

date(timestamp#(left('TRADETIMESTAMP', 19),'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss.ffffff')

Both approaches reduce the granularity so that floating point errors are no longer an error.

Hope that helps,

Re: time difference calculation issue

Wed, 06 May 2015 09:08:29 GMT

Hi Steve,

i have check this it is useful in some cases but not fulfilling in other cases

Re: time difference calculation issue

MarcoWedel — Wed, 06 May 2015 12:15:18 GMT

Hi,

maybe this could be a solution:

tbl_orders:

LOAD ORDKEY,

Timestamp(Round(ORDTIMESTAMP,1/86400000),'YYYY-MM-DD hh:mm:ss.fff') as ORDTIMESTAMP,

Timestamp(Round(TRADETIMESTAMP,1/86400000),'YYYY-MM-DD hh:mm:ss.fff') as TRADETIMESTAMP,

Interval(Round(TRADETIMESTAMP-ORDTIMESTAMP,1/86400000),'hh:mm:ss.fff') as diff

INLINE [

ORDKEY, ORDTIMESTAMP, TRADETIMESTAMP

2014-03-05/512361/23000030323397, 2014-03-05 15:02:03.208001, 2014-03-05 15:02:03.228

2014-03-05/512417/19000137426159, 2014-03-05 14:47:20.619389, 2014-03-05 14:47:20.639

2014-03-05/500171/15000108205982, 2014-03-05 15:01:13.441897, 2014-03-05 15:01:13.461

2014-03-05/500285/14000004008996, 2014-03-05 09:30:56.345519, 2014-03-05 09:30:56.365

2014-03-05/500285/20000002299288, 2014-03-05 12:45:17.366308, 2014-03-05 12:45:17.386

];

TIMECAT:

LOAD Interval(Round(FROM_TIME,1/86400000),'hh:mm:ss.fff') as FROM_TIME,

Interval(Round(TO_TIME,1/86400000),'hh:mm:ss.fff') as TO_TIME,

TIME_CAT

Inline [

FROM_TIME, TO_TIME, TIME_CAT

'00:00:00.000', '00:00:00.020', '10-20ms'

'00:00:00.021', '00:00:00.050', '21-50ms'

'00:00:00.051', '00:00:00.100', '51-100ms'

'00:00:00.101', '00:00:00.200', '101-200ms'

'00:00:00.201', '00:00:00.500', '201-500ms'

'00:00:00.501', '00:00:01.000', '501-1000ms'

'00:00:01.001', '00:00:02.000', '1-2sec'

'00:00:02.001', '00:00:05.000', '2-5sec'

'00:00:05.001', '00:00:10.000', '5-10sec'

'00:00:10.001', '00:00:20.000', '10-20sec'

'00:00:20.001', '00:00:50.000', '20-50sec'

'00:00:50.001', '00:01:00.000', '50-60sec'

'00:01:00.001', '16:00:00.000', '1Min+'

];

IntervalMatch (diff)

LOAD FROM_TIME, TO_TIME

Resident TIMECAT;

hope this helps

regards

Marco

Re: time difference calculation issue

Wed, 06 May 2015 12:26:05 GMT

Thanks,

i will check and let you know

Re: time difference calculation issue

Tue, 12 May 2015 05:35:12 GMT

Dear Marco Wedel,

Thanks a lot. its working as per our need. Thanks a lot.

Regards,

Jitendra