Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rein_nigul
New Contributor

How do VB function processes the variables LOAD Scriptis

Hi

I want to check in the table for each row of data to calculate the reading of one of the cell.

In order to do this, I decided to use a VB function.

function getVariable()

  set v = ActiveDocument.GetVariable("vVariable")

  getVariable = v.GetContent.String

end function

If the Edit module, by testing all of the works (by giving the corresponding value), then Load scriptis is resulting in "-"

How to use variables in data processing, and then LOAD the scripts calculated to save variables.

5 Replies
mov
Esteemed Contributor III

Re: How do VB function processes the variables LOAD Scriptis

Hi Rein,

How you use it in the script?  Can you show this part of the script?

More important - why do you need this function at all?  Variable can be used in the script directly...

Regards,

Michael

rein_nigul
New Contributor

Re: How do VB function processes the variables LOAD Scriptis

Hi Michael,

I need to process the warehouse data so that I can analyze sales by suppliers on the sales documents.

The initial data is missing this information.

The supplier information is only available on the incoming documents

I want to save the incoming quantity and supplier information into the product variables when making the incoming document, and take the supplier name from the variables when creating a sales document

What makes this difficult is that when a new supplier is created and products are taken in, the quantity is not zero.

I tried to use VB macro, which reads in the variable value, processes the variable and then I save the variable value. It works when testing in the edit module, variables are read and saved, but the function does not work in the LOAD script.

Rein

rein_nigul
New Contributor

Re: How do VB function processes the variables LOAD Scriptis

Load script is

stock:

LOAD

*

inline [

ID, DATETIME, QUANTITY, PRODUCT_ID, SUPPLIER_ID, DIRECTION

1, 9.05.2012, 50, 5, 6361, IN

2, 5.06.2012, 16, 5, , OUT

3, 21.06.2012, 1, 5, , OUT

4, 11.07.2012, 1, 5, , OUT

5, 18.07.2012, 1, 5, , OUT

6, 24.07.2012, 2, 5, , OUT

7, 24.07.2012, 3, 5, , OUT

8, 25.07.2012, 4, 5, , OUT

9, 15.08.2012, 2, 5, , OUT

10, 2.10.2012, 2, 5, , OUT

11, 12.10.2012, 35, 5, 6364, IN

];

left join

Tarn_temp:

load

ID,

test(QUANTITY) as AllQuantity

resident stock;

Edit Module:

Function test(quantity)

'Function test()  'testing

'quantity=5  'testing

set v = ActiveDocument.Variables("vbInQuantity") 'read variable

        vbInQuantity =v.GetContent.String

     

     ' msgbox(vbInQuantity) 'testing

       vbInQuantity=quantity+vbInQuantity

  set v = ActiveDocument.Variables("vbInQuantity")' write varaible

        v.SetContent vbInQuantity,true   

end function       

MVP
MVP

Re: How do VB function processes the variables LOAD Scriptis

Hi

You can do all of that in a single load and no module:

LOAD

  ID,

  DATETIME,

  QUANTITY,

  QUANTITY + $(vbInQuantity) As AllQuantity,

  PRODUCT_ID,

  SUPPLIER_ID,

  DIRECTION

inline [

ID, DATETIME, QUANTITY, PRODUCT_ID, SUPPLIER_ID, DIRECTION

1, 9.05.2012, 50, 5, 6361, IN

2, 5.06.2012, 16, 5, , OUT

3, 21.06.2012, 1, 5, , OUT

4, 11.07.2012, 1, 5, , OUT

5, 18.07.2012, 1, 5, , OUT

6, 24.07.2012, 2, 5, , OUT

7, 24.07.2012, 3, 5, , OUT

8, 25.07.2012, 4, 5, , OUT

9, 15.08.2012, 2, 5, , OUT

10, 2.10.2012, 2, 5, , OUT

11, 12.10.2012, 35, 5, 6364, IN

];

HTH

Jonathan

PS - this assumes (as does your example) that vbInQuantity is initialised to some value (not null).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mov
Esteemed Contributor III

Re: How do VB function processes the variables LOAD Scriptis

That is - use variable in the script directly, no function's needed