Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gauravgg
Partner - Creator
Partner - Creator

Convert field into HH:MM:SS

Hi

I have a field as follows:

Format(H.M.S)=0.0.1

Time

0.0.1

12.03.1

12.40.25

I want to convert it in HH:MM:SS as follows

Time

00:00:01

12:03:01

12:40:25

Can anyone help me in this

Thanks in Advance

8 Replies
antoniotiman
Master III
Master III

Time(Time#(Time,'h.m.s'),'hh:mm:ss')

gauravgg
Partner - Creator
Partner - Creator
Author

Hi Antonio

it's not working

antoniotiman
Master III
Master III

Time(Time#('0.0.1','h.m.s'),'hh:mm:ss')  -> 00:00:01

Time(Time#('12.03.1','h.m.s'),'hh:mm:ss')  -> 12:03:01

Time(Time#('12.40.25','h.m.s'),'hh:mm:ss')  -> 12:40:25

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

LOAD * 

  ,Time(Time#(Time, 'h.m.s')) as [New Time]

INLINE [ 

Time

0.0.1

12.03.1

12.40.25

]; 

Result:

Screenshot_1.jpg

Maybe you're doing any transformation before the data gets to format h.m.s?

gauravgg
Partner - Creator
Partner - Creator
Author

Yes

after doing some transformation i am getting that time field

That transformed field  have to convert it into HH:MM:SS, because i want to subtract that HH:MM.SS with other field

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

In this case if you Resident load your transformed field Time into the formulas we suggest you'll get the correct answer.

gauravgg
Partner - Creator
Partner - Creator
Author

then too it is not working

gauravgg
Partner - Creator
Partner - Creator
Author

map1:

mapping load * inline [

x, y

days,'.'

Hr,'.'

Min,'.'

Sec,'.'

] ;

Dim_tic_work_deta_desc:

LOAD

    work_deta_id_pk,

    work_deta_origination,

    work_deta_time_spend_on_previous_action,

   

     SubField( mapsubstring('map1',work_deta_time_spend_on_previous_action),'.',2)&'.'&

    SubField( mapsubstring('map1',work_deta_time_spend_on_previous_action),'.',3)&'.'&

    SubField( mapsubstring('map1',work_deta_time_spend_on_previous_action),'.',4) as [w_d_t_spend_previous(HH.MM.SS)],

   

    tic_work_upda_type_info_work_upda_type_id_fk

FROM [lib://Con_Stage0 (qliksense_netadmin)/heliosdb.tic_work_deta_desc.QVD]

(qvd);

TT:

Load

work_deta_id_pk,

Time(Time#([w_d_t_spend_previous(HH.MM.SS)],'h.m.s')) as [New Time]  

Resident  Dim_tic_work_deta_desc;