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.

    QliKView.png

    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.

    Process1.png

    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.

    Process2.png

    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 Lee Matthews

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