topic Convert string to Date or Timestamp in QlikView

Convert string to Date or Timestamp

rbecher — Thu, 17 Sep 2009 23:43:07 GMT

Hi,

I want to load a timestamp with this format 'MMM DD hh:mm:ss YYYY' from XML files. I tried a lot of things but cannot make it with the timestamp# or date# functions. What is the most simple way to do this? Why this is a problem?

- Ralf

Convert string to Date or Timestamp

disqr_rm — Thu, 17 Sep 2009 23:45:19 GMT

Any chance you can post an XML sample file here?

Convert string to Date or Timestamp

prieper — Thu, 17 Sep 2009 23:49:43 GMT

Never tried, but this format looks a bit unusual so that there might be some internal logic within the QV-formulae standing against it.
Have you tried to load it as string, then fillet it for each piece and compose a new date?

Peter

Convert string to Date or Timestamp

biester — Thu, 17 Sep 2009 23:59:29 GMT

I tried to read in similar values from a txt file, there e.g. a

date(timestamp#(Timestamp,'MMM DD hh:mm:ss YYYY'),'YYYY.MM.DD hh:mm:ss')

is no problem and the field is correctly evaluated as timestamp.

Should it really be something XML-specific?

Rgds,
Joachim

AW:Re: Convert string to Date or Timestamp

rbecher — Fri, 18 Sep 2009 00:32:59 GMT

This is nothing XML specific. I found out that is has to do with some string operations:

test_xml:
LOAD created_at
, timestamp#(mid(replace(created_at,'+0000 ',''),4),'MMM DD hh:mm:ss YYYY') as badTimestamp
FROM C:\Projekte\Qliktech\#bugs\timeline.xml (XmlSimple, Table is [statuses/status]);

The original format is even more unusual (twitter!): 'Wed Sep 16 16:57:36 +0000 2009'

It will work if I adjust the date in the file to the format 'Sep 16 16:57:36 2009' and strip the string operation in the script. But this is no solution.

- Ralf

AW:Re: Convert string to Date or Timestamp

prieper — Fri, 18 Sep 2009 04:21:39 GMT

Why not? I do not get the point of your complaint?

Peter

AW:Re: Convert string to Date or Timestamp

Anonymous — Fri, 18 Sep 2009 09:22:54 GMT

Ralf,

This seems to work. I had to add a trim() around the parsed date to get it through.

date#(trim(mid(created_at,4)),'MMM DD hh:mm:ss +0000 YYYY')

AW:Re: AW:Re: Convert string to Date or Timestamp

rbecher — Mon, 21 Sep 2009 23:19:02 GMT

This is a solution but better is:

timestamp#(mid(created_at, 5),'MMM DD hh:mm:ss +0000 YYYY')

This was my fault, but I wonder why there is no wildcard format sign?