Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

XML timestamp

Hi,

I'm trying to load an xml (internet) document in Qlik Sense. I have some difficulties parsing timestamp fields. My data looks like "2015-02-03T13:55:00". By default, Qlik recognises this as a string field. I tried modifying the timestamp format with SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'; also tried 'YYYY-MM-DDThh:mm:ss[.fff]'. Which unfortunately doesn't work

What seems to work is using the replace function to change the T into a whitespace. (e.g. replace("FIELD1", 'T', ' ') as "FIELD1".

Is there a clean solution for this problem? Otherwise I have to apply this workaround for every timestamp field.

Any help is greatly appreciated.

1 Solution

Accepted Solutions
Employee
Employee

Re: XML timestamp

Unfortunately it is not possible at this stage. Use Marco's work-around, or

Timestamp#(Left(Timestampfield,10) & Mid(Timestampfield,12),'YYYY-MM-DDhh:mm:ss')

But we are looking into how we can make the Qlik engine accept modern timestamps with e.g. 'T' or time zone information. Neither is possible to interpret today.

HIC

3 Replies

Re: XML timestamp

Hi,

this will load the timestamp field in your default timestamp format defined by the variable TimestampFormat:

LOAD Timestamp(Timestamp#(PurgeChar(Timestampfield,'T'),'YYYY-MM-DDhh:mm:ss')) as Timestampfield

FROM yoursource;

hope this helps

regards

Marco

Not applicable

Re: XML timestamp

Hi Marco,

Thanks, yes this probably works. However it is another variation on my forementioned workaround. My question remains, Is it possible to have Qlik Sense correctly recognise timestamp fields when there is a "T" in it? (standard xml timestamp notation). The point is that I'm trying to avoid applying a workaround for every timestamp field I'm using.

Employee
Employee

Re: XML timestamp

Unfortunately it is not possible at this stage. Use Marco's work-around, or

Timestamp#(Left(Timestampfield,10) & Mid(Timestampfield,12),'YYYY-MM-DDhh:mm:ss')

But we are looking into how we can make the Qlik engine accept modern timestamps with e.g. 'T' or time zone information. Neither is possible to interpret today.

HIC