topic Re: DateAdd function in QlikView

DateAdd function

Thu, 18 Jul 2013 19:56:54 GMT

Hi,

I have a function that I’m using in another application that I need to replicate in Qlikview. The function is

DateAdd ("s",{metric_date_store.numeric_qty},{metric_date_store.datetime_dttm} )

It’s adding the value of metric_date_store.numeric_qty to metric_date_store.datetime_dttm. Metric_date_store.numeric_qty is a duration of time stored in seconds.

Is there an equivalent function I can use when setting up my load script? Any guidance would be greatly appreciated.

Re: DateAdd function

luciancotea — Thu, 18 Jul 2013 20:20:45 GMT

You can define your function as a VBA macro and call it from load script: http://luciancotea.wordpress.com/2013/07/04/call-vba-macro-function-from-the-load-script/

Re: DateAdd function

Thu, 18 Jul 2013 20:27:11 GMT

Thanks - I was hoping that QlikView had a function that could add time to an existing date field. I'd prefer that approach versus referencing an external macro. I'll take a look - thanks again.

Re: DateAdd function

luciancotea — Thu, 18 Jul 2013 20:52:26 GMT

To add time to a date is easy. Date is integer value representing how many days passed since 30/12/1899, time is fractional value representing how many miliseconds passed from midnight. Toghether they represent DateTime value.

So, 1 second = 1/24/60/60

1 minute = 1/24/60

Re: DateAdd function

rwunderlich — Fri, 19 Jul 2013 00:15:56 GMT

Should be pretty simple.

timestamp( metric_date_store.datetime_dttm + interval(metric_date_store.numeric_qty,'s') )

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: DateAdd function

Mon, 22 Jul 2013 14:50:34 GMT

Hi - I thought this would work but when I add

timestamp (datetime_dttm + interval(numeric_qty,'s')) as End_Time

to my load script looks like its adding years, not seconds to the datetime_dttm field.

Here is an example:

Description	Start_Time	Duration_In_Minutes	numeric_qty	End_Time
BUWY-DEV	6/22/2013 4:55:12 AM	242	14548.00000	4/21/2053 4:55:12 AM

Re: DateAdd function

Gysbert_Wassenaar — Mon, 22 Jul 2013 14:57:54 GMT

Try: timestamp (datetime_dttm + interval#(numeric_qty,'s')) as End_Time

Or datetime_dttm + numeric_qty/86400

Re: DateAdd function

Mon, 22 Jul 2013 15:23:07 GMT

Thanks! This worked.