Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

qw_johan
Contributor

Help formatting time

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

1 Solution

Accepted Solutions
Not applicable

AW:Re: Help formatting time

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

6 Replies
Not applicable

Help formatting time

Hi,

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

Regards,

Murali

qw_johan
Contributor

Help formatting time

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



Not applicable

AW:Re: Help formatting time

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

MVP
MVP

AW:Re: Help formatting time

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
Contributor

AW:Re: Help formatting time

Hi Roland!

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

qw_johan
Contributor

AW:Re: Help formatting time

Hi tresesco!

Thank you very much for your help. Smile