Posting data to the web services from QlikView

    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


            sXML = sXML + " </row>" + vbCrLf


        sXML = sXML + "</data>"

        'various things we can do with the xml below. uncomment the one you want



        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") "POST", xmlurl, false

        xmlhttp.setRequestHeader "Content-Type", "text/xml"

        xmlhttp.send sXML

        'now get the results from processing


        sResult = xmlhttp.StatusText


    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.



    Same approach can be used to save data into any of 26 data targets supported by AETL

    Macro is based on code published by Lee Matthews

    Write Back to Database via ETL process (using CSV or XML)