convert string column with hours, minutes and seconds to only seconds, request help!!

[hosasahana]

hi,

I have a column which is a string and looks as attached. I need to convert this into only seconds so that I can use it in my code eg: wait_time {'<=60'} or wait_time {'>60'} etc.

Please can someone help ?

[MayilVahanan]

HI @hosasahana 

One of way to achieve this like below.. 

Load *, Evaluate(Replace(Replace(Replace(WaitTime, 'Hour', '*3600+'), 'Minutes', '*60'),'Seconds', '')) as WaitSec Inline
[
WaitTime
1 Hour 1 Minutes
1 Hour 2 Minutes
1 Hour 5 Minutes
0 Seconds
13 Minutes
12 Seconds
];

You can change little bit based on ur requirement

[hosasahana]

hi Mayil,

thanks for responding,

the wait_time field contains lots of values. Is there any other way other than Inline method ?

[MayilVahanan · Accepted solution]

HI @hosasahana 

FYIP, InLine table used for sample purpose. You can try the logic used to calculate the values.

Evaluate(Replace(Replace(Replace(WaitTime, 'Hour', '*3600+'), 'Minutes', '*60'),'Seconds', ''))

[hosasahana]

sorry Mayil,its not working 😞 

[hosasahana]

Pl can anyone help me resolve this?

[hosasahana]

thanks 🙂 it works