Timestamp cuts of fractals after 3 digits

[krameralexander]

Hello community,

I desperatly try to create a timestamp Format with 9 fractals. I got core Data from a Database where the timestamp is very dimituive with 9 fractals after ss

So I changed the format to "SET TimestampFormat='hh:mm:ss.fffffffff';" and enterd also a "Timestamp(Timestamp#([Time], 'hh:mm:ss.fffffffff') ,'hh:mm:ss.fffffffff') AS [TS]" at my import script, but it always cuts of after 3 digits and enters 0 instead. Anyone who can help me? I really need to solve this (Oh ... the information of course is available within the data 🙂 )

[vvira1316]

Sample values will be helpful to help

[sunny_talwar]

QlikView is only able to bring upto 3 fff correctly and becomes weird when you have more than 3 fff... Just an idea: What if you convert it into number and try to bring in? It might still not be able to display correctly the f in the timestamp, but the calculation on them might work.

[krameralexander]

Contract,Date,Time,Depth Level,Bid Quantity,Bid Price,Ask Price,Ask Quantity

ABCD Jan 2019,18.07.2018,06:00:00.193684787,0,,,,

ABCD Jan 2019,18.07.2018,07:55:00.125039582,0,0,43,30,43,70,0

ABCD Jan 2019,18.07.2018,08:00:00.146053080,0,1,43,30,43,70,2

[krameralexander]

Those are exmaple Data ... as you can see, the timestamp contains 9 fractal digits ... If I convert it into timestamp format as shown above it replaces all digits after the third into zeros

[vvira1316]

I'm able to see it in table

Table preview image

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fffffffff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

NoConcatenate
TSTmp:
LOAD * Inline
[
Contract,Date,Time,Depth Level,Bid Quantity,Bid Price,Ask Price,Ask Quantity
ABCD Jan 2019,18.07.2018,06:00:00.193684787,0,,,,
ABCD Jan 2019,18.07.2018,07:55:00.125039582,0,0,43,30,43,70,0
ABCD Jan 2019,18.07.2018,08:00:00.146053080,0,1,43,30,43,70,2
];

NoConcatenate
TSData:
LOAD
Contract,
Date,
Time,
Timestamp#(Time, 'hh:mm:ss.fffffffff') as TSString,
Num(Timestamp#(Time, 'hh:mm:ss.fffffffff')) as TSNum,
[Depth Level],
[Bid Quantity],
[Bid Price],
[Ask Price],
[Ask Quantity]
Resident [TSTmp];

drop Table [TSTmp];

EXIT SCRIPT;

[marcus_sommer]

I think it's caused from the used number-system of Qlik which is limited in regard to the accuracy of a calculation and also to the max. number of digits- some background to it could you find here: Rounding-Errors.

A workaround for you might be to load this field as a string and/or to split this field into a classical time-field and a millisecond-field. By a large dataset you should really consider if you really need this granularity and if to split it - see here why: The-Importance-Of-Being-Distinct.

- Marcus

[krameralexander]

any idea how to create a table with a complete day? 3 digits will be enough but I need a reference day from 00:00:00.000 to 23:59:59.999

Is there a possibility to create such a day?

[marcus_sommer]

Take a look on daystart() and dayend() and if you want to create a master time-table look here: The-Master-Time-Table. The logic could be extended to milliseconds too, but you would create a dimension table with 86 M records and therefore it might be more appropriate to leave the millisecond-part or split it into a further field and dimension-table.

- Marcus