Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Text toTimestamp# not working

Hey Guys,

I have an API extracted table which has a column with values similar to this 2016-05-03 10:57:54+12:00. This is text as i tested it using istext() which returned '-1'

I am trying to convert data in this column from Text to Timestamp, so i can split this later into Date and time respectively

I attempted  TimeStamp#(MaxString(<field_Name>,1) ) but the result is still the same "Text" value. it does not return value in 'TimeStamp' Type.

Not sure what i am doing wrong.  my objective is get column with values like 2016-05-03 10:57:54+12:00 to be converted into regular timestamp of local type and use it to split them into Date and time respectively.


Hope this make sense. Any help will be hugely appreciated.


Thanks. 

1 Solution

Accepted Solutions

Re: Text toTimestamp# not working

try this

Date(floor(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss')),'DD-MM-YYYY') as Date

Time(frac(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss'),1/24),'hh:mm:ss') as Time

4 Replies
zhadrakas
Valued Contributor

Re: Text toTimestamp# not working

Hello Pranav,

i have tested this with following script and it works fine.

try to add the Format to the timestamp function:

Timestamp#(MaxString(<field_Name>,1), 'DD-MMM-YYYY hh:mm:ss')

testscript:

time:
Load
Timestamp#('14-JUN-2015 17:14:26', 'DD-MMM-YYYY hh:mm:ss') as time
AutoGenerate 1;

transform:
Load
date(time) as date,
time(time) as time,
day(time) as day,
month(time) as month,
year(time) as year
resident time;

Re: Text toTimestamp# not working

try this

Date(floor(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss')),'DD-MM-YYYY') as Date

Time(frac(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss'),1/24),'hh:mm:ss') as Time

Re: Text toTimestamp# not working

or may be like this:

LOAD *,

           Date(Floor(TimeStampField)) as DateField,

           Time(Frac(TimeStampField)) as TimeField;

LOAD *,

           TimeStamp(TimeStamp#(Left(FieldName, 19), 'YYYY-MM-DD hh:mm:ss')) as TimeStampField

FROM Source;

Not applicable

Re: Text toTimestamp# not working

Thank you very much guys for taking the time out and replying.

All the suggestions provided worked. Have marked the very first reply to this question from Kaushal  as correct answer.

Cheers,

Pranav

Community Browser