Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

call VBscript function/procedure from loading script

Hi all,

I wrote vbscript macro that export data to excel.

When running the macro from the "edit module" screen (by clicking on the "Test" button") it works.

I'm trying to call my function from the loading script.

I tried to write the macro as a procedure (with SUB, END SUB) and call from script with "CALL" - but it throws "script line error" message.

I tried to write the macro as a function (with Function, END Function) and call from script with SET var = MyFunc() or with LOAD MyFunc(), but no data was exported.

How should I call my macro? Does there is any security issue?

I read all discussions about this issue and tried to do everything but it still doesn't work...

Can someone please advise? thanks!!!

8 Replies
israrkhan
Valued Contributor II

Re: call VBscript function/procedure from loading script

have you tried with button and trigger,

create a button and add action on button, "run a macro" an see if it works...

Re: call VBscript function/procedure from loading script

During the load it didn't exist the ActiveDocument and all methods which needs this basic-point will fail. Instead you could use a PostReload-Trigger.

- Marcus

Re: call VBscript function/procedure from loading script

Your function method should work.

Re: call VBscript function/procedure from loading script

Re: call VBscript function/procedure from loading script

  1. QUALIFY *; 
  2.  
  3. Table1: 
  4. LOAD *, RecNo() as RecNo INLINE [ 
  5.     MARK1, MARK2 
  6.     CH: WV2ZZZ7HZ9H119390, ENGINE NO: AXB179634 
  7.     CH: WV2ZZZ7HZ9H119548, ENGINE NO: AXB179429 
  8.     CHASSIS NOSmiley FrustratedHSRE77508U101822, ENG NO:K2424-1019151 
  9.     CH: KL1PM5E51AK528096, ENG: F18D4009613KA 
  10.     CH: WV2ZZZ7HZ9H100620, ENG NO: AXB178969 
  11.     CH: AHTLT58E406009706, ENG: 1NR0178419 
  12.     CH: AHTLT58E206008912, ENG: 1NR0162953 
  13.     CH: AHTLT58E606008833, ENG: 1NR0162819 
  14. ]; 
  15.  
  16. Table2: 
  17. LOAD *, RecNo() as RecNo INLINE [ 
  18.     MARK1, MARK2 
  19.     CHASS# WV2ZZZ7HZ9H119390, E# AXB179634 
  20.     C# WV2ZZZ7HZ9H119548, ENG NO: AXB179429 
  21.     CH: AHTLT58E206008912, ENG: 1NR0162953 
  22.     CH: KL1PM5E51AK528096, ENG: F18D4009613KA 
  23.     CHS# WV2ZZZ7HZ9H100620, ENG#: AXB178969 
  24.     CH: AHTLT58E406009706, ENG: 1NR0178419 
  25.     VIN NOSmiley FrustratedHSRE77508U101822, ENG:K2424-1019151 
  26.     CH: AHTLT58E606008833, ENG: 1NR0162819 
  27. ]; 
  28.  
  29. UNQUALIFY *; 
  30.  
  31. NoConcatenate 
  32.  
  33. tabLinks: 
  34. LOAD * Resident Table1; 
  35. Join (tabLinks) 
  36. LOAD * Resident Table2; 
  37. Left Join (tabLinks) 
  38. LOAD Table1.RecNo, 
  39.      Table2.RecNo, 
  40.      levenshtein(Table1.MARK1,Table2.MARK1) as LevDistMARK1, 
  41.      levenshtein(Table1.MARK2,Table2.MARK2) as LevDistMARK2 
  42. Resident tabLinks; 
  43.  
  44. DROP Fields Table1.MARK1, Table2.MARK1, Table1.MARK2, Table2.MARK2 From tabLinks; 

using this module / vbs-function:

  1. '******************************************************************************************************** 
  2. '* 
  3. '*  VBScript function implementing Levenshtein-Distance-algorithm 
  4. '*  source: http://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Levenshtein_distance#VBScript 
  5. '* 
  6. '******************************************************************************************************** 
  7.  
  8.  
  9. Function levenshtein( a, b ) 
  10.   Dim i,j,cost,d,min1,min2,min3 
  11.  
  12. ' Avoid calculations where there there are empty words 
  13.   If Len( a ) = 0 Then levenshtein = Len( b ): Exit Function 
  14.   If Len( b ) = 0 Then levenshtein = Len( a ): Exit Function 
  15.  
  16. ' Array initialization 
  17.   ReDim d( Len( a ), Len( b ) ) 
  18.  
  19.   For i = 0 To Len( a ): d( i, 0 ) = i: Next 
  20.   For j = 0 To Len( b ): d( 0, j ) = j: Next 
  21.  
  22. ' Actual calculation 
  23.   For i = 1 To Len( a ) 
  24.   For j = 1 To Len( b ) 
  25.                         If Mid(a, i, 1) = Mid(b, j, 1) Then cost = 0 Else cost = 1 End If 
  26.  
  27.   ' Since min() function is not a part of VBScript, we'll "emulate" it below 
  28.   min1 = ( d( i - 1, j ) + 1 ) 
  29.   min2 = ( d( i, j - 1 ) + 1 ) 
  30.   min3 = ( d( i - 1, j - 1 ) + cost ) 
  31.  
  32.   If min1 <= min2 And min1 <= min3 Then 
  33.   d( i, j ) = min1 
  34.   ElseIf min2 <= min1 And min2 <= min3 Then 
  35.   d( i, j ) = min2 
  36.   Else 
  37.   d( i, j ) = min3 
  38.   End If 
  39.   Next 
  40.   Next 
  41.  
  42.   levenshtein = d( Len( a ), Len( b ) ) 
  43. End Function 

hope this helps

regards

Marco

MVP
MVP

Re: call VBscript function/procedure from loading script

Hi,

Check this there is an example in this link

Calling VB function (macro) in Load script to find common string length in two strings

Regards,

Jagan.

Not applicable

Re: call VBscript function/procedure from loading script

Thank you all for your help!!!! I used Marcus_Sommer suggestion (PostReload-Trigger) and it's working!

Re: call VBscript function/procedure from loading script

please mark your thread as answered.

thanks

regards

Marco

Community Browser