Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

Timestamp format error

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

1 Solution

Accepted Solutions
Highlighted
Contributor

Re: Timestamp format error

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 (

View solution in original post

13 Replies
Highlighted
Contributor

Re: Timestamp format error

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'))

Highlighted
MVP
MVP

Re: Timestamp format error

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

Highlighted
Contributor

Re: Timestamp format error

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

Highlighted

Re: Timestamp format error

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

Highlighted

Re: Timestamp format error

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

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

Re: Timestamp format error

Try this, May be?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Valued Contributor

Re: Timestamp format error

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

Highlighted
Contributor

Re: Timestamp format error

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 (

View solution in original post

Highlighted
Contributor

Re: Timestamp format error

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