topic Re: Timestampconversion: Timestamp format with milliseconds in chart in QlikView

Timestampconversion: Timestamp format with milliseconds in chart

Thu, 19 Nov 2015 14:13:43 GMT

Hello community,

I got the date and Time with the following two format:

Sep 15 2015 10:23:10.345.678

15.09.2015 10:23:10.345.678

the timestamp is in an xml-file which I want to import.

I want to create a graph with the complete timestamp on x-axis. The data is from more than one day. Because of this I need the date an the time with the milliseconds.

I have no ideas how i can do this. I have already tried several ways, but i doesn't work.

I got no numerical Values on x-axis in the chart.

Re: Timestampconversion: Timestamp format with milliseconds in chart

swuehl — Thu, 19 Nov 2015 16:18:11 GMT

What are the last six digits denote? For milliseconds, only three digits would be needed, right?

In general, you need to read your field values in so that QV interpretes them as timestamps:

Why don’t my dates work?‌

Get the Dates Right

Re: Timestampconversion: Timestamp format with milliseconds in chart

Thu, 19 Nov 2015 21:17:19 GMT

There are two different Timestamps in the xml-files:

the last six digits are because of the simulation-software. hh:mm:ss.milliseconds.microseconds.

Maybe it is easier to cut the microseconds but I have no idea.

There is another problem to solve. I get a start time of simulation

and for each simulationstep a timestamp

the result for each simulationstep for the plot in qlikview should look like this

15.09.2015 10:23:10.345.678

i have tried serveral things with set and timestamp#, date#.....

SET DateFormat='DD.MM.YYYY';

//SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss.fff.fff';

TTT:

LOAD

timestamp#(Time, 'MMM dd YYYY hh:mm:ss.fff.fff) as Time

timestamp#(Start, 'ddd MMM YY hh:mm:ss') as StartTime

Stamp

FROM (XmlSimple, Table is [test]);

and tried to change the settings of the diagramm but i didn't get it.

Re: Timestampconversion: Timestamp format with milliseconds in chart

maxgro — Thu, 19 Nov 2015 21:31:40 GMT

could you post the test.xml file?

Re: Timestampconversion: Timestamp format with milliseconds in chart

MarcoWedel — Fri, 20 Nov 2015 00:22:54 GMT

Hi,

one solution could be:

tabTimestamps:
LOAD *,
    Timestamp(Alt(Timestamp#(Left(DateTime,Len(DateTime)-4),'DD.MM.YYYY hh:mm:ss.fff'),Timestamp#(Left(DateTime,Len(DateTime)-4),'MMM DD YYYY hh:mm:ss.fff')),'DD.MM.YYYY hh:mm:ss.fff') as DateTime2
INLINE [
    DateTime
    Sep 15 2015 10:23:10.345.678
    15.09.2015 11:23:10.345.678
];

hope this helps

regards

Marco

Re: Timestampconversion: Timestamp format with milliseconds in chart

Fri, 20 Nov 2015 10:26:05 GMT

Hi,

now it works

I'm able to create a chart with the timestamps. I tried several times, but I didn' get it. Thanks for your help.

a part of the xml-file is in the attachments. (Original filesize up to 10GB, because of the duration an number of events of the simulation)

What I want to do is:

- I want to import the file in Qlikview

- create an Absolute Timestamp for each event like DateTime2 15.09.2015 12:23:45.948 for charts

- create a Chart with contains the values of the different Variables vs Timestamp

- and if it is possible a dot or something else with the name of the Event, when a Event occurs

regards

Mat

Re: Timestampconversion: Timestamp format with milliseconds in chart

Shubham_Deshmukh — Mon, 12 Aug 2019 05:53:24 GMT

Hi @MarcoWedel ,

Tried same code, then I saw your code it is same for my case as well, still not getting why it's not working, M I missing something?, need to calculate max from several dates.

Given format : MMM DD YYYY hh:mm:ss:fff TT

Required Format : MM-DD-YYYY hh:mm:ss

[Sheet1]:
LOAD
[Report],
[Sales],
[reportRunDate]
from excel;

Res:
Load
max(Timestamp(Timestamp#(reportRunDate,'MMM DD YYYY hh:mm:ss:fff TT),'MM-DD-YYYY hh:mm:ss')) as maxDate

Resident Sheet1;

Drop table Sheet1;

Regards,

Shubham