Load data in the script with a macro

[nstefaniuk]

Hello all.

I am searching for a way to load data from a macro in the script, without using a temporay file.

I have a macro that extracts data from a specific source, unreachable by the standard connectors. The data is stored in a csv file created on the fly. Then I can read the csv file with the standard script.

I am searching for a direct way to add the data in a Qlikview table with the macro, to avoid the temporary csv file. I could loop on data and create an INLINE line for each row, but it would be ugly and I fear that the perfomance would be low.

Thanks a lot for your help on this issue.

[vgutkovsky]

It's been a while since I've done this, but if I remember correctly you can create macros directly in the script. The syntax is something like:

SUB mySub

(code here)

End Sub

CALL mySub

Note that there are no semicolons here. Within the sub it's just regular VBS syntax. If this doesn't work for you, you can also try creating a standard .vbs file to create your CSV and then trigger your QVW reload on completion of the .vbs.

Regards,

Vlad

[nstefaniuk]

Yes, I know it.

But my question is : how, in the macro, add lines in a QV table on the fly? For example if my macro do a loop on the month number (12 today) and I want that my macro create 12 lines with "Hello World <monthname>" and I want these 12 lines to be added directly in a QV table, without writing a temporary text file or an INLINE, how could I do that ?

Thanks

[vgutkovsky]

See attached for a very simplified example.

Cheers,

Vlad

[rwunderlich]

Assuming you have to fetch the data in the module code, here's how I do it. Pick a delimiter for your rows that won't appear in your data. Create a module function that returns your rows as a single string with the delimiter between rows. Like this for your example:

Function MakeSomeLines (count)

          ret = ""

          For i = 1 to count

                    ret = ret & "Hello World " & cstr(i) & ";"

          Next

          MakeSomeLines = Left(ret, Len(ret)-1)  ' Drop trailing semicolon

End Function

In your LOAD statement, call the function with any necessary parms. Split into rows using substring:

MyTab:

LOAD subfield(MakeSomeLines(month(today(1))), ';') as Hello

AutoGenerate 1;

BTW, if your data already has newline chars then the delimiter and subfield parm is chr(10)

(I'm aware that the above can be done in script, but the example matches the poster's question).

-Rob

http://robwunderlich.com

[nstefaniuk]

Thanks Vlad but in your case, the number of lines is defined in the script, when the number of lines in my case if given by the result of the macro. It means that the macro can retrieve me 1 or 1000 lines and I need to add these lines in a Qlikview table.

Very interesting method Rob, I understand that subfield is a way to generate more than 1 line in a table with only 1 line in the source. Very very interesting. Unfortunately, it means that if I have 10 000 lines to add, my macro should retrieve the data of the 10 000 lines concatenated in 1 string, and I fear that I can reach a limit.

In fact I search to append data in qlikview table in VB script. Something like ActiveDocument.Table.AppendData(bla...)

Maybe I could Create a chart on the fly, then read it and store in a table ?

Thanks

subfield

[rwunderlich]

You can't manipulate ActiveDocument during load, but you can after the load. Perhaps even in the OnPostReload event. You could use the dynamic update feature -- Document.DynamicUpdateCommand -- to add data.

Easy to do in Desktop, some obstacles in the server. I believe you can use dynamic update in QV Developer without restriction. To use it in the server requires licensing an additional feature. Also, OnPostReload in the server cannot run macros if you chose to implement this with OnPostReload.

-Rob

http://robwunderlich.com

[nstefaniuk]

Hmmm ok.

So here are my cases/tests:

- On load, call a function that retrieves all data and stores in a text file

- Still on load, read the file

=> issue : unsafe macro must be enable on server.

- On load, call a function that retrieves all data concatenated in a string, then split with Qlikview function subfield

=> issue : dangerous if there is too much lines, the string would be like a CLOB.

- On load, call a function that stores data in an VB array

- Still on load, loop on the array (with VB functions) to read content

=> issue : a VB variable can't survive to a call, and so can't be used to store data

- On load, call a function that stores data in numerous Qlikview variables, created and filled on the fly

- Still on load, loop on the variables (with autogenerate) to read content

=> issue : a Qlikview variable can't be created in the load because ActiveDocument does not exist

- On open, call a function that stores data in numerous Qlikview variables, created and filled on the fly

- On load, loop on the variables (with autogenerate) to read content

=> issue : seems that macro can't be called on OnOpen on server

- On open, call a function that stores data in Qlikview tables with DynamicUpdate.

- On load, loop on the tables

=> issue : DynamicUpdate needs a license on server

- On load, call a function that retrieves the data 1 time and count the results

- Still on load, loop on the count and call a function that retrieves each line

=> issue : as we can't store the data with VB script, the macro will be send 1 time to count + 1 time for each line to retrieve. If the macro is long to run and there are a lot of lines, the execution will be very long.

So, it seems that I have no solution. Maybe some other ideas? Thanks

[rwunderlich · Accepted solution]

>So here are my cases/tests:

>- On load, call a function that retrieves all data and stores in a text file

>- Still on load, read the file

>=> issue : unsafe macro must be enable on server.

"Unsafe macro" setting on server sounds scary, but it's not really. I would guess that something close to half the installations have this set on.

>- On load, call a function that retrieves all data concatenated in a string, then split with Qlikview function subfield

>=> issue : dangerous if there is too much lines, the string would be like a CLOB.

Have you tested this? I regularly use this technique to retrieve thousands of lines with no problem. I just ran a quick test and extracted 20k lines from a 1MB+ string with no problem. Worst case, you can make multiple calls to your datasource for something like 1000 lines each -- if your datasource supports chunking like that.

>- On load, call a function that stores data in an VB array

>- Still on load, loop on the array (with VB functions) to read content

>=> issue : a VB variable can't survive to a call, and so can't be used to store data

The macro module can use global variables and code (anythng defined outside a Sub or Function). Those global vars will survive between calls.

Global  code will be executed the first time the macro module is used -- eg when a function is called from the script. Global code will also be executed when the macro editor is opened. It's a bit tricky to play with this in development. Best to close and reopen the doc when doing final testing.

Another possibility is to build the csv file in a seperate batch process before the reload. If you have Publisher, this can be scheduled as an external task that runs before the reload. You could also run this process with a script EXECUTE statement but you would have to allow EXECUTE in the script settings.

-Rob

[nstefaniuk]

Thanks Rob.

In fact the "issues" I have given are constraints from administrators (no unsafe macros) or architecture (don't do a concatenate / split of data that could, even if it's rare corrupt or lost data).

For my personnal use I would have used the text file with unsafe macro or your technic with subfield.

I will try the use of global variables, but I have already tried without result. Maybe I have badly tested it.

Your last solution is probably the good one, I think we will do that, because this solution is accepted by Administrators AND architecture.

Thanks a lot.