topic call sub from script issue in QlikView

call sub from script issue

Sat, 30 Apr 2011 13:50:40 GMT

Good morning,

I'm trying to call a sub from a script page. I'm sure it is something simple but I can't find it.

This is a qvd builder and I'm calling the logger sub from within a loop. the error I get is "error calling logger".

thanks for any thoughts..

---------------------------------------

SUB logger

set tn = ActiveDocument.Variables("_table_name").GetContent.String

set qn = ActiveDocument.Variables("_qvd_name").GetContent.String

set r = ActiveDocument.Variables("_result").GetContent.String

DIM fso, MyFile, strFile, strText

strFile="C:\qlikview_logs\log.log"

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strFile) Then

Set MyFile = fso.OpenTextFile (strFile, 8, True)

Else

Set MyFile = fso.CreateTextFile(strFile)

MyFile.WriteLine("log_dtm,table_name,qvd_name,result")

End If

strText = Now() & "," & tn & "," & qn & "," & r

MyFile.WriteLine(strText)

MyFile.Close

END SUB

----------------------------------------

SCRIPT

--------------------------------

$(Include=C:\qlikview_config\config.vbs);

// SEE CONFIG FILE ABOVE on the target system or Ctrl + Alt + V in QV for variables overview

//SET _qvd_name='choa.qlikview_qvd_tables';

// include connect file

$(Include=$(_include_path)connect_odbc_qlikview.txt);

tables:

SELECT * FROM CHOA.QLIKVIEW_QVD_TABLES

WHERE ACTIVE = 'Y'

;

let rows = NoOfRows('tables');

for i=0 to $(rows)-1

let _table_name = peek('TABLE_NAME',$(i),'tables');

let _qvd_name = peek('QVD_NAME',$(i),'tables');

$(_qvd_name):

SELECT * FROM $(_table_name);

IF ERRORCOUNT = 0 or IsNull(ERRORCOUNT) THEN

STORE $(_qvd_name) INTO $(_qvd_path)$(_qvd_name).qvd (qvd);

DROP TABLE $(_qvd_name);

let _result = 'ok';

CALL LOGGER

ELSE

let _result = 'error';

CALL LOGGER

ENDIF

call sub from script issue

Oleg_Troyansky — Sat, 30 Apr 2011 16:20:04 GMT

If I understand you code correctly, you are trying to call a VB subroutine from a load script... You can't do it using CALL.

CALL is used when you need to call a load script sub. For example:

sub Test

load .....

end sub

...

CALL Test;

I think the only way to call a VBScript routine is to include it as a function in a load statement. I might be wrong though, since I haven't done too much of that...

Something like this should work (can't guarantee the exact syntax):

VBScript:

function Test (parm1, parm2)

...

end function

----------------

Load script:

Dummy:

load Test(1,2) as DummyField

autogenerate(1)

;

call sub from script issue

Sun, 01 May 2011 12:03:52 GMT

Thanks Oleg. It does not appear that my FileSystemObject is available from the script page so I'll have to go another direction. I appreciate your input

call sub from script issue

Sun, 01 May 2011 12:36:59 GMT

hi Tod ,

instead of using "Call" :

In the script line try using this variable decleration :

Let vEngage_Sub = LOGGER() ;

It might do the trick ...

Kind regards,

Moshe Golan

QlikView Consultant

call sub from script issue

rwunderlich — Mon, 02 May 2011 00:18:38 GMT

While not an answer to your OP, another option is to do your logging in script instead of a macro. There's nothing in your example that can't be done in script -- without needing system access. See attached example.

call sub from script issue

Mon, 02 May 2011 01:03:55 GMT

Thanks Rob, it looks like that will work. Actually I was trying to avoid using a module but I couldn't figure out how.

I will miss not having total control over the document with an fso but I'm not really sure I'll need it.

Thanks to all that posted