Solved: Re: time difference calculation issue - Qlik Community

Report Inappropriate Content · ‎2015-05-06

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;

MarcoWedel · ‎2015-05-06

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

View solution in original post

Report Inappropriate Content · ‎2015-05-06

Can anyone help me ??

PrashantSangle · ‎2015-05-06

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

tresesco · ‎2015-05-06

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.

Report Inappropriate Content · ‎2015-05-06

Hi Max,

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

PrashantSangle · ‎2015-05-06

Hi,

What is your expected output??

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Report Inappropriate Content · ‎2015-05-06

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.

i

Report Inappropriate Content · ‎2015-05-06

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..

PrashantSangle · ‎2015-05-06

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

tresesco · ‎2015-05-06

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.