QlikView documentation and resources.
It is very common task to post data from QlikView into external systems or databases.
In this article we will explain how to place orders using QlikView
The dashboard below is being used by one of the Presidents to order flowers for famous people.
When user presses the button Order some flowers the following macro is executed
'A simple function to convert the contents of a table to XML
'NOTE: Ensure that a variable named vObjectID has been created in document
' and that you assign the relevant table ID to this variable before
' calling the function. The table ID should be set to be the table name
' where the data is to be saved.
'--------------------------------------------------------------------------
sub createXML
'get ID of table to convert from variable
sTableID = ActiveDocument.GetVariable("vObjectID").GetContent.String
sTableID = replace(sTableID, """", "")
'now build the xml from the table contents
sXML = "<?xml version='1.0'?>" + vbCrLf
sXML = "<data table='" + sTableID + "'>" + vbCrLf
set oTableBox = ActiveDocument.GetSheetObject( sTableID )
for iRow = 1 to oTableBox.GetRowCount-1
sXML = sXML + " <row id='" + CStr(iRow) + "'>" + vbCrLf
for iCol = 0 to oTableBox.GetColumnCount-1
sField = oTableBox.GetField(iCol).Name
sValue = oTableBox.GetCell(iRow,iCol).Text
sXML = sXML + " <" + sField + ">" + sValue + "</" + sField + ">" + vbCrLf
next
sXML = sXML + " </row>" + vbCrLf
next
sXML = sXML + "</data>"
'various things we can do with the xml below. uncomment the one you want
'-----------------------------------------------------------------------
'msgbox(sXML)
PostToWebService sXML, sTableID
end sub
'submit the XML to a web service for processing
'------------------------------------------------------------
sub PostToWebService(sXML, sTableID)
'set this variable to correct URL where ASP page is located
xmlurl = "http://localhost:8000"
'Create xmlhttp opject and submit
Set xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
xmlhttp.open "POST", xmlurl, false
xmlhttp.setRequestHeader "Content-Type", "text/xml"
xmlhttp.send sXML
'now get the results from processing
xmlhttp.WaitForResponse
sResult = xmlhttp.StatusText
msgbox(sResult)
end sub
Macro generates XML and sends it over network to Advanced ETL Processor (AETL)
AETL runs two processes. First one is constantly listening to certain TCP/IP port and saving every incoming XML message into separate file.
This approach allows us to reprocess the data if necessary.
Second process runs every minute; it moves XML files into WorkInProgress folder sends emails, moves files into Completed folder.
Note:
Same approach can be used to save data into any of 26 data targets supported by AETL
Macro is based on code published by lms