topic Re: Timestamp format error in QlikView

Timestamp format error

kmswetha — Fri, 04 Aug 2017 11:02:35 GMT

I'm having a file name where I should be extracting timestamp from the file.

However I'm able to convert to date but not time stamp

My file format is XYZ_20_11_2016_00_12

by using string functions I'm able to extract date, 11/20/2016 but its throwing error for time stamp.

I'm using date(date#)) but it is not working for timestamp(timestamp#))

any suggestions

Re: Timestamp format error

kusumanchir — Fri, 04 Aug 2017 11:11:11 GMT

Hi Swe,

Please try using this,

=timestamp(timestamp#(replace(mid('XYZ_20_11_2016_00_12',5,10),'_','/')&' ' & replace(mid('XYZ_20_11_2016_00_12',16,5),'_',':'),'DD/MM/YYYY hh:mm'))

Re: Timestamp format error

tresB — Fri, 04 Aug 2017 11:12:07 GMT

How are you using timestamp(Timestamp#(... ; what is it giving you as output and what do you expect?

Re: Timestamp format error

kmswetha — Fri, 04 Aug 2017 11:13:14 GMT

it's not giving anything.In textbox it's just showing -

Re: Timestamp format error

Miguel_Angel_Baeyens — Fri, 04 Aug 2017 11:15:49 GMT

Which expression are you using and what format code are you providing?

Re: Timestamp format error

PrashantSangle — Fri, 04 Aug 2017 11:17:19 GMT

Try simple

=Timestamp(Timestamp#(mid('XYZ_20_11_2016_00_12',index('XYZ_20_11_2016_00_12','_')+1),'DD_MM_YYYY_hh_mm'))

Replace XYZ_20_11_2016_00_12 with your fieldName.

Regards

Re: Timestamp format error

Anil_Babu_Samineni — Fri, 04 Aug 2017 11:19:16 GMT

Try this, May be?

=Date(Date#(Purgechar(Left(Replace(KeepChar('XYZ_20_11_2016_00_12', '0123456789_'),'_', '/'),11),'/'),'DDMMYYYY'),'MM/DD/YYYY')

Re: Timestamp format error

tomasz_tru — Fri, 04 Aug 2017 11:21:51 GMT

If the string is the same lenght all the time:

timestamp(timestamp#(right(FIELD,16), 'DD_MM_YYYY_HH_MM'),'YYYY-MM-DD HH:MM:SS') as ts

Re: Timestamp format error

kmswetha — Fri, 04 Aug 2017 11:22:33 GMT

surprise part is, if I hardcode and do its working

=timestamp(timestamp#(right('XYZ_20_11_2016_00_12',16),'DD_MM_YYYY_HH_MM'),'M/D/YYYY HH:MM:SS tt')

but not if I replace file name (

Re: Timestamp format error

kmswetha — Fri, 04 Aug 2017 11:23:25 GMT

I did exactly same, it works but if replace FIELD with actual name its not working

Re: Timestamp format error

tresB — Fri, 04 Aug 2017 11:25:20 GMT

One correction.

M-Month

m-Minute

=timestamp(timestamp#(right('XYZ_20_11_2016_00_12',16),'DD_MM_YYYY_HH_mm'),'M/D/YYYY HH:mm:SS tt')

Re: Timestamp format error

PrashantSangle — Fri, 04 Aug 2017 11:26:53 GMT

Can you provide us what are you trying.

One more thing in format MM - Repesent Month

mm-repesent minutes.

Regads,

Re: Timestamp format error

MarcoWedel — Fri, 04 Aug 2017 22:42:45 GMT

Hi,

another example:

LOAD SomeField,
    Timestamp(Timestamp#(Mid(FileBaseName(),Index(FileBaseName(),'_',-5)+1),'DD_MM_YYYY_hh_mm'),'MM/DD/YYYY hh:mm') as FileNameTimestamp,
    FileBaseName() as FileBaseName
FROM [*XYZ*.txt] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

hope this helps

regards

Marco

Re: Timestamp format error

rubenmarin — Sat, 05 Aug 2017 12:00:53 GMT

Hi Swe, seems the problem is with the filename, how are you retrieving and using the filename?