QlikView Documents

[etl_tools]

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

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