Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Hi Michele,
Try to convert the field to numeric by using the expression below:
Date(Date#([Reload Duration], 'hmm'),'hh:mm:ss'
Hi Michele,
Try this:
LOAD *,
Time(Durata,'hh:mm:ss') as Durata2;
LOAD * Inline [
Durata
0.099537037037037,
0.00037037037037037
];
Already tried but it returns null
Already tried and also your solution is not working...
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.
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.
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;
May be you want to provide sample data file.
if possible share your qvd here. Put 10 sample records in that qvd and share.
Regards,