topic Re: Text toTimestamp# not working in QlikView

Text toTimestamp# not working

Tue, 03 May 2016 03:15:31 GMT

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.

Re: Text toTimestamp# not working

zhadrakas — Tue, 03 May 2016 06:22:38 GMT

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

Kushal_Chawda — Tue, 03 May 2016 06:50:25 GMT

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

sunny_talwar — Tue, 03 May 2016 08:42:43 GMT

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;

Re: Text toTimestamp# not working

Thu, 05 May 2016 02:32:44 GMT

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