Convert field into HH:MM:SS

[gauravgg]

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

[antoniotiman]

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

[gauravgg]

Hi Antonio

it's not working

[antoniotiman]

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]

LOAD *  
  ,Time(Time#(Time, 'h.m.s')) as [New Time] 
INLINE [  
Time
0.0.1
12.03.1
12.40.25 
];  

Result:

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

[gauravgg]

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]

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

[gauravgg]

then too it is not working

[gauravgg]

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;