topic Re: Convert decimal number to duration in App Development

Convert decimal number to duration

micheledenardi — Fri, 17 Aug 2018 08:25:24 GMT

Good morning,

i would like to convert during script load some decimal numbers to time hh:mm:ss like below:

Original Duration Format	Formatted Duration
0.099537037037037	02:23:20
0.00037037037037037	00:00:32

if i insert the fields ( in origianal format) on a table ad set "duration" as format, qlik sense calculate correctly the conversion.

I Would like to obtain this result by using load script.

I've already tried:
- time([Reload Duration]) as [Reload Duration]

- date([Reload Duration],'hh:mm:ss') as [Reload Duration])

- Interval(interval#(ceil([Reload Duration]),'D'), 'hh:mm:ss') as [Reload Duration]

and other solution but no-one is working...

Re: Convert decimal number to duration

Arthur_Fong — Fri, 17 Aug 2018 10:15:46 GMT

Hi Michele,

Try to convert the field to numeric by using the expression below:

Date(Date#([Reload Duration], 'hmm'),'hh:mm:ss'

Re: Convert decimal number to duration

jmmolero — Fri, 17 Aug 2018 10:44:35 GMT

Hi Michele,

Try this:

LOAD *,

Time(Durata,'hh:mm:ss') as Durata2;

LOAD * Inline [

Durata

0.099537037037037,

0.00037037037037037

];

Re: Convert decimal number to duration

micheledenardi — Fri, 17 Aug 2018 11:17:56 GMT

Already tried but it returns null

Re: Convert decimal number to duration

micheledenardi — Fri, 17 Aug 2018 11:19:49 GMT

Already tried and also your solution is not working...

Re: Convert decimal number to duration

jmmolero — Fri, 17 Aug 2018 11:44:14 GMT

After set the Time format, are you manipulating the field? For example:

1- Add a value to the field:

[table]:

LOAD *,

Time(Durata,'hh:mm:ss') as Durata2;

LOAD * Inline [

Durata

0.099537037037037,

0.00037037037037037

];

Concatenate(table)

LOAD Durata2 + 0.0001 as Durata2

Resident table;

2-Concatenate to a previous table without time format

[table]:

LOAD * Inline [

Durata2

0.099537037037037,

0.00037037037037037

];

Concatenate

LOAD *,

Time(Durata,'hh:mm:ss') as Durata2;

LOAD * Inline [

Durata

0.099537037037037,

0.00037037037037037

];

In this examples qlik set numerical format although you force the Time format.

Re: Convert decimal number to duration

micheledenardi — Fri, 17 Aug 2018 11:52:34 GMT

Absolutely no, i don't make data manipulations after this passage.

Your solutions works only if i load data from inline statement.

When i put your solution in a standard load from qvd, it doesn't woks.

Re: Convert decimal number to duration

vvira1316 — Fri, 17 Aug 2018 12:01:38 GMT

try

NoConcatenate
[LoadData]:
Load * Inline
[
"Start", "End"
'2018-08-17 05:07:32', '2018-08-17 05:08:04'
'2018-08-17 00:30:43', '2018-08-17 02:54:03'
];

NoConcatenate
FormatData2Process:
Load
[Start]
,[End]
,Num(Time([Start], 'YYYY-MM-DD HH:MM:SS')) as StartTimeNum
,Num(Time([End], 'YYYY-MM-DD HH:MM:SS')) as EndTimeNum
Resident [LoadData];

NoConcatenate
ProcessData:
Load
[Start]
,[End]
,StartTimeNum
,EndTimeNum
,Num(EndTimeNum - StartTimeNum) as Delta
,Interval(Time([End], 'YYYY-MM-DD HH:MM:SS') - Time([Start], 'YYYY-MM-DD HH:MM:SS')) as Duration
,Interval(Time([End], 'YYYY-MM-DD HH:MM:SS') - Time([Start], 'YYYY-MM-DD HH:MM:SS'), 'HH:MM:SS') as FormattedDuration
Resident FormatData2Process;

Drop Tables [LoadData], FormatData2Process;

Re: Convert decimal number to duration

vvira1316 — Fri, 17 Aug 2018 12:02:50 GMT

May be you want to provide sample data file.

Re: Convert decimal number to duration

PrashantSangle — Fri, 17 Aug 2018 12:04:55 GMT

if possible share your qvd here. Put 10 sample records in that qvd and share.

Regards,

Re: Convert decimal number to duration

marcus_sommer — Fri, 17 Aug 2018 12:11:33 GMT

Try it with something like this:

time(num#('0.00037037037037037', '#,##0.###########', '.', ','))

or maybe

time(num#(left('0.00037037037037037', 15), '#,##0.###########', '.', ','))

- Marcus

Re: Convert decimal number to duration

jmmolero — Fri, 17 Aug 2018 12:44:48 GMT

Please, share sample data and load script.

I try this:

[table]:
LOAD Interval([Reload time],'hh:mm:ss') as [Reload time format],
*;
LOAD *,
Timestamp#(End,'DD/MM/YYYY hh:mm:ss') - Timestamp#(Start,'DD/MM/YYYY hh:mm:ss') as [Reload time];
LOAD * Inline [
Start, End
'12/08/2018 05:07:32', '12/08/2018 05:08:04'

];

I stored the table in qvd and loaded the table again from qvd, the time format remained..

Regards,