Qlik Community

Qlik Sense App Development

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

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
micheledenardi
Valued Contributor

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

11 Replies
Highlighted
Partner
Partner

Re: Convert decimal number to duration

Hi Michele,

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

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




Highlighted
Partner
Partner

Re: Convert decimal number to duration

Hi Michele,

Try this:

LOAD *,

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

LOAD * Inline [

Durata

0.099537037037037,

0.00037037037037037

];

durata.PNG

Highlighted
micheledenardi
Valued Contributor

Re: Convert decimal number to duration

Already tried but it returns null

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

Highlighted
micheledenardi
Valued Contributor

Re: Convert decimal number to duration

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

Highlighted
Partner
Partner

Re: Convert decimal number to duration

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.

Highlighted
micheledenardi
Valued Contributor

Re: Convert decimal number to duration

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.

Highlighted
vvira1316
Valued Contributor II

Re: Convert decimal number to duration

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

Highlighted
vvira1316
Valued Contributor II

Re: Convert decimal number to duration

May be you want to provide sample data file.

Highlighted

Re: Convert decimal number to duration

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.