XML timestamp

[#1]

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.

[MarcoWedel]

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

[#3]

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.

[hic · Accepted solution]

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