Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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