Help formatting time

[qw_johan]

Hi!

I am loading a textfile that has a "time_duration" field. Unfortunately all the time data isn't formatted the same way.
The "time" can be formatted both like "1 min 32 sec" and like just "97" (meaning 97 seconds) .
How can I handle this in Qlikview? I would like all data in that field to be displayed the same minutes and seconds.

I have been trying to use time() and time#() without success.

Thanks

[#2]

Hi,

Could you please post few rows of sample data from the text file?

Regards,

Murali

[qw_johan]

Hi Murali!

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

[#4 · Accepted solution]

Hello qw_johan,

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)
as time_duration

Regards, Roland

[tresesco]

Have a look what i wanted to try :

Orig_table:
Load * Inline [
//your sample data

;
Transformed:
Load *,
Time(Time#(If(Isnum(Time_duration),Time_duration,Num#(SubField(Time_duration,' ',1))*60 +Num#(Subfield(Time_duration,' ',3),'#') ),'s'),'mm:ss') as Time,
resident Orig_table;
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.

Regards, tresesco

[qw_johan]

Hi Roland!

This was exactly what I wanted but didn't know how to do.
Thank you very much!

[qw_johan]

Hi tresesco!

Thank you very much for your help.