topic Re: Add Decimal Hours to dd/mm/yyyy in QlikView

Add Decimal Hours to dd/mm/yyyy

prees959 — Tue, 26 Sep 2017 18:30:51 GMT

Hi,

I have a 'Date', 'Start' and 'Duration' field. The Duration is a decimal hours field eg, 12.5 rather than 12:30.

For example, if I have 01/10/2017 as a Date, 20:00, as a Start time, I need to create an End DateTime field to show, in this

example, 02/10/2017 20:30.

Is this possible?

Thanks,

Phil

Re: Add Decimal Hours to dd/mm/yyyy

sergio0592 — Tue, 26 Sep 2017 18:51:56 GMT

Hi,

For add 12.5 hours and obtain you End date time

=Timestamp(Timestamp#(Date&' '&Start time,'DD/MM/YYYY hh:mm')+0.52,'DD/MM/YYYY hh:mm')

Re: Add Decimal Hours to dd/mm/yyyy

sunny_talwar — Tue, 26 Sep 2017 18:53:21 GMT

May be this

TimeStamp(Date + Interval#([Start time]*60*60, 's')) as [End DateTime]

Re: Add Decimal Hours to dd/mm/yyyy

rodrigo_dittric — Tue, 26 Sep 2017 18:57:10 GMT

If you need to convert decimal to exact time and concatenate in date field, You can use this:

Example: = date(today()) & ' ' & Time(12.5/24)

Re: Add Decimal Hours to dd/mm/yyyy

Frank_Hartmann — Tue, 26 Sep 2017 19:14:20 GMT

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

Load *,
Date(num(Date#(Date(date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')&' '&Date#(Start,'hh:mm'),'DD/MM/YYYY hh:mm'))+num#(Duration,'##,###')/24,'DD/MM/YYYY hh:mm') as End;


LOAD * INLINE [
    Date, Start, Duration
    01/10/2017, 20:00, "12,5"
];

Re: Add Decimal Hours to dd/mm/yyyy

MarcoWedel — Tue, 26 Sep 2017 20:50:52 GMT

Hi,

another example:

table1:
LOAD *,
    Timestamp(Date+Start+Duration) as End;
LOAD Date,
    Start,
    Interval(Duration/24,'hh:mm') as Duration
INLINE [
    Date, Start, Duration
    01/10/2017, 20:00, 12.5
    03/10/2017, 12:34, 5.8
    05/10/2017, 23:45, 2.2
];

hope this helps

regards

Marco

Re: Add Decimal Hours to dd/mm/yyyy

prees959 — Wed, 27 Sep 2017 09:13:36 GMT

I went with Franks's answer. But really appreciate everyone's responses.