Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER 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...

11 Replies
Arthur_Fong
Partner
Partner

Hi Michele,

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

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




jmmolero
Partner
Partner

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

Already tried but it returns null

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

micheledenardi
Specialist II
Specialist II

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

jmmolero
Partner
Partner

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

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.

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.