topic Execute a Macro inside load script in QlikView

Execute a Macro inside load script

Tue, 14 Sep 2010 17:06:17 GMT

Hi All,

is possible execute an Qlikview macro inside a load script?

Tks

Execute a Macro inside load script

Tue, 14 Sep 2010 17:57:49 GMT

Sure! Just create it and then use it in the macro - Qlikview will look for it at load time if it cannot find a native function. For example I created this function which I use in a script:

Function MonthDiff (startdate, enddate)
' return number of months between two dates
If IsDate(startdate) and IsDate(enddate) Then
MonthDiff = DateDiff("m", startdate, enddate)
End If
End Function

Regards,

Gordon

Execute a Macro inside load script

Wed, 15 Sep 2010 10:34:58 GMT

Tks Gordon for your reply.

I created a function and into load script I execute it by CALL command.

Call MonthDiff()

Regards.

Execute a Macro inside load script

Wed, 09 Feb 2011 17:06:26 GMT

Hi, I tried this very same code and get "Script line error" in Call MonthDiff()

what could it be, has the syntax changed?

thanks,

Execute a Macro inside load script

Miguel_Angel_Baeyens — Wed, 09 Feb 2011 17:16:47 GMT

Hello,

If you are calling the function from the load string, do as follows instead

LET vExecFunction = MonthDiff();

If the function in the macro editor (CTRL + M) is built using Sub / End Sub instead of Function / End Function, then use CALL in the script.

Hope that helps.

Execute a Macro inside load script

Wed, 09 Feb 2011 17:24:47 GMT

If you use the code as in my example then it is a function not a sub - you call a sub but not a function. So, to use my code you would simply have in the script:

MonthDiff(date1, date2)

Regards,

Gordon

Execute a Macro inside load script

marcel_olmo — Wed, 09 Feb 2011 18:40:50 GMT

Hey Gordon, I'm just trying the same as you. Loading a macro inside the loading script.

I have many problems doing that.

When I write in my loading script :

call myFunction()

I got an error message.

My suggestions is :

1- You can only call the macros in VBScript ( I have my macros in jscript).

2-I've tried both ways to get the same result (functions in javascript and vbscript), and I still can't get it work.

Any Ideas about what I'm doing wrong??

Many thanks in advance!!

Execute a Macro inside load script

Wed, 09 Feb 2011 18:54:32 GMT

I dont undertstand what the problem is - I use the function (VBS) in several documents and they work just fine in v9sr4 and previously in v8.2.

Is your problem just the dates you are passing to the function being invalid or the wrong format?

Regards

Gordon

Execute a Macro inside load script

marcel_olmo — Thu, 10 Feb 2011 10:04:49 GMT

Well, in fact I'm not using your dates function. Actually, I have a document with "jscript" macros. And I would like to execute a macro (that generates xml files) before reloading the document with the new day information.

I know that a useful solution would be separate the execution in two files. First execute the document that contains the macro, and then reload the document with the information generated in the previous document.

But I still want to do it in the same doucment, I think it would be cooler ;).

Hope you can give me some advices about that.

Many thanks in advance!!

Execute a Macro inside load script

Thu, 10 Feb 2011 10:47:15 GMT

How about using the document OnOpen event to execute the macro? You would need to be able to identify from within the macro when the action should be fired as it will fire every time (including user access) which you can do by passing a variable and value through the command line interface using the /v switch.

Regards,

Gordon

Execute a Macro inside load script

marcel_olmo — Thu, 10 Feb 2011 12:35:53 GMT

Thanks for your suggestion.

Finally I've decided to create a .bat file which calls the QV document, adding a flag variable that tells the document to generate the xml files, reload the document, save it and finally close it.

Now works easily. Thanks!!

Execute a Macro inside load script

Wed, 22 Feb 2012 13:12:25 GMT

HI Gordon,

Hope u r fine,I used the code as placed in your example

Function MonthDiff (startdate, enddate)

' return number of months between two dates

If IsDate(startdate) and IsDate(enddate) Then

MonthDiff = DateDiff("m", startdate, enddate)

End If

End Function

and try to invoke it in my script as follows

LET vExecFunction = MonthDiff(Date(AddMonths(Today(),-4)),Today());

and debug the script, I find vExecFunction variable holding null value

could you please let me know why it so.

Re: Execute a Macro inside load script

Thu, 04 Apr 2013 13:40:59 GMT

hi,

I have tried several things to make this work but it is not working. I have a funktion that should automatically send a mail.

I call my function in the load script like this.

let mail = sendMailTest();

this works fine if I open qv and press the load script button, but when I load the qvw in the management console it doesnt work can anyone explain me the reason why??

regards,

Re: Execute a Macro inside load script

Anonymous — Thu, 11 Apr 2013 14:23:14 GMT

Hi MT,

Ive probably testing the same function. From the loading script a macro should be started and mail incl a pdf should be sent. I dont know why this is such a pain..

I heard sth about the OnPostReload Triggers. Maybe somehow it will work through triggers.

Unfortunately I dont find this "OnPost" trigger.

On execution trigger doesnt work in my case...

cheers chesterluck

Re: Execute a Macro inside load script

Mon, 26 Aug 2013 04:42:19 GMT

Srinivasan Rangaswamy

I am having a code which execute at the script and display the message at the vb Script (Both are haveing issue at the call code any Tought process/Guidence)

1. I am loading at the script and

2 showing the completed time at the vbscript message box.

here is my peace of code.Can any one of you help me out on this.

QV Script

LET vStart = num(now());

Store * from FactTemp into FactTemp.qvd (qvd) ;

LET vRunTime = interval(now() - $(vStart),'mm:ss');

call alp(vRunTime) ;

VB Macro

function alp(vRunTime)

msgbox vRunTime;

end function

or
consider this example

QV script

call alp(20)

vb script

function alp(vRunTime)

msgbox vRunTime

end function

Re: Execute a Macro inside load script

Tue, 24 Mar 2015 19:40:14 GMT

Hey Gordon Savage

I think the only reason why your example isn't working within my test application is because my Qlikview isn't recognizing DateDiff as a valid function. Despite that, your logic makes complete sense. In order to use the Macro Function in the Load Script, you call it like the following in the Load Script

Let vMonths = MonthDiff( Today(), Today() - 600)

I researched other sources and confirmed that you cannot utilize Macro Functions within Expressions on the front-end of Qlikview. In other words, I cannot utilize MonthDiff() in a Text Box.

This post contains an alternative function which worked in my application; it utilizes the same method, but a different macro function, which is why I believe the only reason MonthDiff() doesn't work is because of DateDiff() (Call VBA macro function from the load script | Lucian Cotea)

//Create this function in the Module
function Inc(Value)
Inc = Value+1
end function

//Use in Load Script
LET _VAR1 = Inc(1);

Last thing, there is no need to create a trigger for this macro in the document settings because it is a function. If it were a Sub, we'd need to create a trigger as mentioned previously.

I hope this helps!

Re: Execute a Macro inside load script

cmccafferty — Wed, 19 Dec 2018 15:35:04 GMT

Hi, Can I piggyback onto this query.

I've created a function that works okay (see attached screenshots), but was hoping to have it execute whilst the script loads. Have I done it properly?

Thanks