topic VBScript question in QlikView

VBScript question

jduenyas — Tue, 22 May 2012 22:58:47 GMT

Hi All

I have created a function in the Edit Module in VBScript which given 2 dates return the elapsed time in Days,Hours, Mins, taking into account work hours (8AM - 6PM), weekends and holidays. So if an employee started a job at 4/6/2012 9:03:00 and fiinished 4/12/2011 16:60:00, the function will evaluate that to 5 Days, 7 Hours, 27 Minutes.

The function works correctly when running it from within the Edit Module however it does not when I try to deploy it from within an expression.

In the expression I pass to the function (TimeCard) the following:

=TimeCard(Date1, Date2) where Date1 and Date2 are fields in one of the tables which I read from our database.

Am I doing anything wrong that no values are returned?

Thanks

Josh

VBScript question

Wed, 23 May 2012 11:04:33 GMT

Hi,

When you say in the expression, you mean that you are using the function inside Edit Module only, right?

Can you post your application?

Also, this type of calculation you can do in native qlikview itself, variables may come handy here. I don't see need of a macro.

You should not use macros extensively, they themselves are slow in execution as well as make whole application itself slow.

Ashutosh

Re: VBScript question

jduenyas — Fri, 25 May 2012 22:41:22 GMT

Hi Ashutosh,

Sorry i am so late with my reply to you. I got haung up with other burning issues in our company.

The attached is a sample QV and data and a sample VBScript function which is called from the expression but does not give the results I am looking for.

Any input will be appreciated.

Thanks

Re: VBScript question

s_uhlig — Sat, 26 May 2012 13:14:29 GMT

Hi Josh,

it's not possible to use vbscript functions in an expression. However you can do the calculation during load in the script.

load DATESTART,DATEEND,MultiplyByFive(MO) as MO5; ...

Regards

Sven

Re: VBScript question

Mon, 28 May 2012 06:45:19 GMT

Hi jduenyas,

Sorry for late reply.

You can not call the functions/subroutines defined inside macro section in the expressions of an object. That subroutine can be executed in macro section only.

But if you want a custom function then you can use variables as a function.

Also, for your requirement, you don't need to use a function you can directly do it inside an expression without using any function.

Please find the attached applicaiton to see examples for both of the cases.

Let me know, if any issue.

Ashutosh

Re: VBScript question

jduenyas — Tue, 29 May 2012 20:43:55 GMT

Sven

thank you for you reply. However your suggestion did not work and I cannot understand why.

The attached is the real function which I am trying to use with some data.

Any suggestion is appreciated.

Thank you

Josh

Re: VBScript question

jduenyas — Tue, 29 May 2012 20:48:39 GMT

Once again thank you Ashutosh.

I do not think I can use your suggestion as the function I showed initially was much more simplified than the one I am using.

Attached you will find the "real" function. I would not know how to embed that into a variable as you suggested.

If you have any suggestions or can enlighten me, I will appreciate it.

Thanks

Josh

Re: VBScript question

s_uhlig — Tue, 29 May 2012 23:21:42 GMT

Hi Josh,

give

TimeCard(''&EmailReceivedOn,''&QuotedOn) AS ElapsedTime

a try, which will force the string representation of your dates, otherwise they will be just numbers.

Regards

Sven

Re: VBScript question

Wed, 30 May 2012 06:55:51 GMT

Hi jduenyas,

You don't need to put this function in a variable.

It can work fine from macro itself.

The problem why it was not workin in your case was because of date format not being properly parsed into vbscript.

I have corrected it.

I have changed the variables where you assign dtDate1 and dtDate2.

Please find the attached application, let me know if any issue.

Ashutosh

Re: VBScript question

jduenyas — Wed, 30 May 2012 15:12:46 GMT

You are the BEST!!!

Re: VBScript question

Thu, 31 May 2012 09:48:02 GMT

Thanks

But I believe BEST in Qlikview world is copyrighted by our dear John Witherspoon

Regards

Ashutosh