topic Re: Convert String to Time stamp format in QlikView

Convert String to Time stamp format

Wed, 01 Aug 2012 11:02:03 GMT

Hi all,

It's an urgent requirement for me to finish now.

How to convert an different type format field into Timestamp format ?

Consider the field in my table contains Datefield

Datefield

Fri Apr 06 07:45:47 2012

Fri Apr 06 07:45:50 2012

Fri Apr 06 07:45:55 2012

Sat Apr 07 11:15:12 2012

How can i convert above structure to timestamp format ?

Greatly appreciated to all your suggestions.

Thanks in advance.

Regards,

Venkat

Re: Convert String to Time stamp format

swuehl — Wed, 01 Aug 2012 11:22:11 GMT

Try something like

=timestamp(timestamp#(mid('Fri Apr 06 07:45:47 2012',5),'MMM DD hh:mm:ss YYYY'))

or in your load script

LOAD

Datefield,

timestamp(timestamp#(mid(Datefield,5),'MMM DD hh:mm:ss YYYY')) as Timestamp,

...

FROM ...;

your standard month names format should match the month names abbr. in your field, e.g.:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Re: Convert String to Time stamp format

Wed, 01 Aug 2012 11:47:54 GMT

Hi ,

Thanks for reply i tried your code, getting required format after placing in calculated dimension.

Now in my chart the "Timestamp" sort order is in-proper, how to make Timestamp sort order in asc.

Please find the attached sample app.

Re: Convert String to Time stamp format

swuehl — Wed, 01 Aug 2012 11:59:05 GMT

Just sort by Numeric Value in Sort tab.

You can also format your Timestamp field in the load script, just by adding the format needed to the timestamp() function.

Re: Convert String to Time stamp format

Wed, 01 Aug 2012 12:36:52 GMT

Hi,

thanks a lot . I got exactly sort order by making sort by expresssion "TimeStamp".

And one more help from you when i'm trying directly in below format in "TimeStamp" it is showing empty in TimeStamp field.

Why it is happeining ? Can i give directly "MMM/DD/YYYY hh:mm:ss".

timestamp(timestamp#(mid([Time Data Recorded],5),'MMM/DD/YYYY hh:mm:ss')) as TimeStamp

Thanks in advance.

Regards,

Venkat

Re: Convert String to Time stamp format

swuehl — Wed, 01 Aug 2012 12:41:35 GMT

There are two functions involved, timestamp#() to interpret your string as timestamp (not changing the displayed text format) and timestamp() to format the displayed timestamp. Both take a format code as second argument:

timestamp(timestamp#(mid([Time Data Recorded],5),'MMM DD hh:mm:ss YYYY'),'MMM/DD/YYYY hh:mm:ss') as Timestamp,

Re: Convert String to Time stamp format

Wed, 01 Aug 2012 15:17:09 GMT

Thanks a lot Swuehl. Your post is very helpful to me to complete the project.

Regards,

Venkat.

Re: Convert String to Time stamp format

Thu, 02 Aug 2012 08:45:00 GMT

Hi,

One more help from you.

I'm using the below line in edit script and trying to make sort by field "TimeStamp". But not able to sort the PLDate field same as TimeStamp field.

Trim(Left(Mid([Time Data Recorded],5),7)&Right([Time Data Recorded],4)) as PLDate

What might be the cause or need to follow some other way ?

Thanks in advance.

Regards,

Venkat

Re: Convert String to Time stamp format

swuehl — Thu, 02 Aug 2012 09:01:27 GMT

It seems that you want to create a Date only from your timestamp and format it.

Don't do this by concatenating strings.

Try always to create date or timestamp values with a text as well as a numerical representation, so you can use QV date and time functions and you can calculate with dates / timestamps, for example like this for your request:

Date(daystart(Timestamp),'MMM DD YYYY') as PLDate

(if you need to create Timestamp first from your Time Data Recorded, use above as preceding load or replace Timestamp with the expression used to create Timestamp (see above)).

LOAD *,

Date(daystart(Timestamp),'MMM DD YYYY') as PLDate;

LOAD

[Time Data Recorded],

timestamp(timestamp#(mid([Time Data Recorded],5),'MMM DD hh:mm:ss YYYY'),'MMM/DD/YYYY hh:mm:ss') as Timestamp,

...

FROM ...;

Hope this helps,

Stefan

Re: Convert String to Time stamp format

Thu, 02 Aug 2012 09:27:14 GMT

Awesome Swuehl. Really thanks for your support and sharing your knowledge to me.

Once again thanks a lot.

Regards,

Venkat.