topic Re: SQL Convert in QlikView syntax in QlikView

SQL Convert in QlikView syntax

Mon, 30 Mar 2015 15:08:19 GMT

Hi all,

I have a column with a StartTime, followed by columns with extra time in seconds.

For example:

RecNo StartTime TalkTime WaitTime

1 1330 57 120

Startime in this case means 13:30 (hh:mm).

Now, I would like to come up with the EndTime by adding the TalkTime and WaitTime to the StartTime.

In SQL the following works:

CONVERT(VARCHAR(8), DATEADD(SS, Talktime + WaitTime, CallSegmentStart), 108) as EndTime

How can I get this to work in QlikView syntax?

Thanks!

Mon, 30 Mar 2015 21:38:31 GMT

Hello,

Get end time:

=Time#('1330', 'hhmm') + (57 + 120) / 1440

Format end time:

=Time(Time#('1330', 'hhmm') + (57 + 120) / 1440, 'hh:mm')

Still, take care about:

=Time(Time#('2300', 'hhmm') + (30 + 31) / 1440, 'hh:mm') -> 00:01 (next day!)

PS: 1440 = 60 * 24 (minutes in 24 hrs)

Tue, 31 Mar 2015 09:08:20 GMT

Thanks @Andrei!

The time was in seconds (which I didn't mention) and the StartTime is in 'hmm' so the formula that works for me is:

Time(Time#('1330', 'hmm') + (30 + 31 ) / 86400, 'hh:mm:ss')