timestamp issue

[saradhi_it]

hi

i have timestamp its not in proper format i tried different ways to convert them its not working ,there are several columns like this

Can anybody help for this issue please check the attached  sample data

[YoussefBelloum]

Hi,

what is the expected output ?

[saradhi_it]

i need in seconds

[viveksingh]

Hi,

Please be clear on the requirement. show your input and expected ouput.

I'm unable to open your excel. DO you want timestamp along with seconds or convert time into seconds?

this may help you.

Convert HH:MM:SS into seconds | Qlik Community

[saradhi_it]

hi vivek

Actually data format was not good

example:    2:18(mm:SS)       but in excel it was converted  to  2:18:00 AM(hh:mm:ss) i tried above links they are not working  there are several columns i can't convert each and every column my user was leave to change the data formatting i need complete some POC on these data

[gireesh1216 · Accepted solution]

Hello Pardha,

Use below script to get actual time format.

LOAD *,

if(len(SubField(time_stamp,':',1))=0,time('00'&time_stamp,'hh:mm:ss'),If(len(SubField(time_stamp,':',1))>0,Time(time_stamp,'hh:mm:ss'))) as new_time_stamp

;

LOAD A as name,

     B as time_stamp

FROM

//Change ur excel file location

(ooxml, no labels, table is Sheet1);

RESULT:  

[saradhi_it]

thanks mate