Recursive Data Load

[#1]

I have a load process which makes a SOAP call (currently via a macro using xmlhttp.open "POST") to retrieve XML data. The macro then kicks off a reload of the document. It's working well, except the SOAP call limits my data retrieval to a maximum of 100 items at a time. If the XML has a value in an element that we shall for purposes of this discussion call "NextID", then I need to pass that ID back to another SOAP request, and continue retrieving data until NextID comes back null.

Here is what I did so far:

1) OnOpen trigger sets a cycle count variable to 1 and vNextID variable to nothing then calls the macro.

2) Macro makes initial SOAP call and reloads doc. If the cycle count is 1, it sends no special next ID (defaults to beginning of data).

3) Load script sets vNextID variable to whatever the last NextID was in the XML (with a peek).

4) I have an OnPostReload trigger set to run the same macro but it never re-runs.

Any suggestions of how to handle this? I can't figure out how to get the macro/load to keep rerunning until there is no more data.

I'm also concerned about comments I've seen on the QV Community that macros do not run in server mode.

Currently on v.10.

Help!?

Sue

[rwunderlich · Accepted solution]

I believe OnPostReload cannot run a macro in the server environment, but I can't find the doc.

Another approach might be to do the looping thing in the vbs module and call it once as a function. So in your load script:

LOAD DoSoapCall() as x AUTOGENERATE 1;

LOAD * FROM soapout.xml;

and in your module:

Function DoSoapCall

Make SOAP calls, looping through 100 recs at a time and write all output to file "soapout.xml"

DoSoapCall = "whatever"  ' could be count of records or whatever.

End Function

-Rob

[#3]

Awesome idea, Rob. Thank you.

In the meantime, I kept playing with it and got it to work by putting a "call x" in the sub x macro. The macro then checks the variable updated at the end of the reload to see whether it should keep going. Loops continuously until it hits that "all done" flag...

Sue

[#4]

P.S., based on the behavior I was getting, I don't think the "ActiveDocument.DoReload" in the macro triggers the OnPostReload action. If I would manually reload, the action was triggered but not via the macro's doing the reload.

[#5]

Hey, Rob. I hope you're still out there...

I am using your idea and it's working great in devel. But when I deploy it to QEMC (v.10), it looks like QlikView is not able to use FSO to write out the XML file. I'm guessing that QEMC doesn't have access to the File System Object (unless there's some setting someplace???).

FileName = "Try3.xml"

set fso = CreateObject("Scripting.FileSystemObject")

set s = fso.CreateTextFile(FileName, True)

s.writeline(xmlhttp.responseText)

s.Close()

Any ideas on how to get the data from the SOAP response into a file???

[rwunderlich]

You have to set the equivalent of "Allow System Access" in QEMC to allow the CreateObject call to work on server reloads. The setting in QEMC is "Allow unsafe macro execution on server" in System, Setup, Qlikview Servers, Security.

-Rob

http://robwunderlich.com

[#7]

THANK YOU so much Rob!!! You're a life saver!

[rwunderlich]

You're welcome. I take mine with cream

[omarlq20]

hello,

I have a problem because I have a SOAP web service that sent me five parameters and returns the information in an xml, I need to know is how I can do to send the parameters and return me xml file by QlikView using macros, read the information and to use

[rwunderlich]

There are some examples here on the forum on how to call a web service using macros. You can also use the script load from URL method if the service supports GET parameters. There are some examples here as well.

One issue that comes up is parsing the returned XML. The QV script xml parser does not support strings as input, only files (last time I checked). So you have two alternatives I know of:

1. Write the returned XML to a file and load using the script support. You can do the writing with an FSO object in the macro. Script STORE doesn't work because it wants to create CSV format which is not proper XML.

2. Parse it yourself using string functions.

-Rob