Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
sunitha_chellaiah

Convert time format

Hi All,

I  have field with time values mentioned as 

24 min 37 sec,

25 min 31 sec,etc

I want to convert this in time format as HH:MM:SS .

 

 

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try this:

 

Load 
     [Resolution Time],
	 Time(
	 Alt(Time#(TimeFormatted,'hhAAmmBBssCC'),
	 		Time#(TimeFormatted,'mmBBssCC'),
	 				Time#(TimeFormatted,'hhAA'),
	 					Time#(TimeFormatted,'mmBB'),
	 						Time#(TimeFormatted,'ssCC'))
	 	, 'hh:mm:ss') as ConvertedTime;
Load	 						
	 Replace(Replace(Replace(Replace([Resolution Time], 'hour','AA'),
      'min', 'BB'), 'sec', 'CC'), ' ','') as TimeFormatted,
	 [Resolution Time]
From <>;

 

View solution in original post

6 Replies
tresesco
MVP
MVP

Do you also have value like:

2 hours 10 mins 30 secs     

or, it would be like 

130 mins 30 secs                 ?

 

sunitha_chellaiah
Author

Yes. I have values like 

2 hours 10 mins 30 sec

tresesco
MVP
MVP

Could you share a set of sample data (at least 10 values) with various formats possibilities?

sunitha_chellaiah
Author

Resolution Time
18 min 52 sec
21 min 20 sec
23 min 19 sec
21 min 11 sec
22 min 31 sec
19 min 49 sec
21 min 41 sec
24 min 37 sec
25 min 31 sec
14 min 31 sec
22 min 21 sec
18 min 4 sec
25 min
25 min 19 sec
19 min 30 sec
21 min 33 sec
21 min 57 sec
18 min
20 min 22 sec
31 min
25 min 8 sec
21 min 8 sec
20 min 18 sec
26 min 36 sec
17 min 19 sec
24 min 28 sec
15 min
17 min 27 sec
23 min 16 sec
20 min 8 sec
19 min 9 sec
20 min 25 sec
34 min 33 sec
23 min 30 sec
33 min 43 sec
24 min 11 sec
23 min 5 sec
21 min 59 sec
19 min 46 sec
25 min 39 sec
23 min 19 sec
25 min 5 sec
28 min 53 sec
20 min 33 sec
22 min 19 sec
26 min 29 sec
20 min 33 sec
22 min 2 sec
22 min 27 sec
27 min 32 sec
36 min 28 sec
31 min
28 min 56 sec
22 min 38 sec
18 min 26 sec
13 min 26 sec
20 min 4 sec
34 min 59 sec
28 min 53 sec
28 min 3 sec
20 min 32 sec
33 min 6 sec
25 min 54 sec
20 min 17 sec
15 min 50 sec
20 min 32 sec
19 min 12 sec
20 min 44 sec
16 min 39 sec
29 min 17 sec
35 min 23 sec
24 min 12 sec
18 min 52 sec
16 min 30 sec
27 min 6 sec
27 min 33 sec
17 min 32 sec
13 min 13 sec
12 min 40 sec
14 min 43 sec
39 min 2 sec
18 min 23 sec
1 min
 

 

tresesco
MVP
MVP

Try this:

 

Load 
     [Resolution Time],
	 Time(
	 Alt(Time#(TimeFormatted,'hhAAmmBBssCC'),
	 		Time#(TimeFormatted,'mmBBssCC'),
	 				Time#(TimeFormatted,'hhAA'),
	 					Time#(TimeFormatted,'mmBB'),
	 						Time#(TimeFormatted,'ssCC'))
	 	, 'hh:mm:ss') as ConvertedTime;
Load	 						
	 Replace(Replace(Replace(Replace([Resolution Time], 'hour','AA'),
      'min', 'BB'), 'sec', 'CC'), ' ','') as TimeFormatted,
	 [Resolution Time]
From <>;

 

sunitha_chellaiah
Author

Thank You !  It's working