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: 
micheledenardi
Specialist II
Specialist II

Convert decimal number to duration

Good morning,

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

Original Duration FormatFormatted Duration
0.09953703703703702:23:20
0.0003703703703703700:00:32

  

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

2018-08-17 10_15_25-_TemplateEmptyApp(1) _ Sistema di visualizzazione modello dati - Qlik Sense.png

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

2018-08-17 10_16_11-TemplateEmptyApp(1) - Il mio nuovo foglio _ Fogli - Qlik Sense.png

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...

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
11 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Hi Michele,

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

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




jmmolero
Partner - Creator
Partner - Creator

Hi Michele,

Try this:

LOAD *,

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

LOAD * Inline [

Durata

0.099537037037037,

0.00037037037037037

];

durata.PNG

micheledenardi
Specialist II
Specialist II
Author

Already tried but it returns null

2018-08-17 13_17_29-TemplateEmptyApp(1) _ Sistema di visualizzazione modello dati - Qlik Sense.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
micheledenardi
Specialist II
Specialist II
Author

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

2018-08-17 13_18_45-TemplateEmptyApp(1) _ Sistema di visualizzazione modello dati - Qlik Sense.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
jmmolero
Partner - Creator
Partner - Creator

Hi

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.

micheledenardi
Specialist II
Specialist II
Author

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.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
vvira1316
Specialist II
Specialist II

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;

time.PNG

vvira1316
Specialist II
Specialist II

May be you want to provide sample data file.

PrashantSangle

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂