topic AW:Re: Help formatting time in QlikView

Help formatting time

qw_johan — Mon, 11 Apr 2011 14:44:11 GMT

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

Help formatting time

Mon, 11 Apr 2011 14:47:22 GMT

Hi,

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

Regards,

Murali

Help formatting time

qw_johan — Mon, 11 Apr 2011 16:35:29 GMT

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

AW:Re: Help formatting time

Mon, 11 Apr 2011 17:43:24 GMT

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

AW:Re: Help formatting time

tresB — Mon, 11 Apr 2011 18:17:17 GMT

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

AW:Re: Help formatting time

qw_johan — Mon, 11 Apr 2011 18:46:53 GMT

Hi Roland!

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

AW:Re: Help formatting time

qw_johan — Mon, 11 Apr 2011 18:50:12 GMT

Hi tresesco!

Thank you very much for your help.