Here are some sample data. The data in time_duration field is formatted in two different ways. How can I make it same?
I have tried time() function with no success. Also tried this...
//If time_duration string doesn't have "min" in it then convert the data
if(FindOneOf(time_duration, 'min')=0, Time(Time#(time_duration,'ss'),'hh:mm:ss'), time_duration) as time_duration
type number time_duration
09FB 184461 1 min 16 sec
09FB 895299 88 --> 1 min 28 sec
09FB 184461 97 --> 1 min 37 sec
09FB 184461 2 min 34 sec
09FC 467018 233 --> 3 min 53 sec
09FB 467378 0 min 16 sec
I am not sure if you can achieve it using the time-functions. I would try something like that (assuming you don't have hours, but this should be an easy add-on then):
if(FindOneOf(time_duration, 'min')=0, floor(time_duration/ 60) & ' min ' & Mod( time_duration, 60) & ' sec', time_duration)
Have a look what i wanted to try :
Load * Inline [
//your sample data
Time(Time#(If(Isnum(Time_duration),Time_duration,Num#(SubField(Time_duration,' ',1))*60 +Num#(Subfield(Time_duration,' ',3),'#') ),'s'),'mm:ss') as Time,
drop Table Orig_table;
you can use the exactly same format of expression depending on your data (if it does not vary in format from what you exactly gave in sample data).
Hope this helps.