Qlik Community

QlikView Documents

Documents for QlikView related information.

Write back to database via XML output or web service

Employee
Employee

Write back to database via XML output or web service

Attached is a zip file containing a sample application (a QVW, MDB, ASP page, and Doco) with code that can write back to a database from QlikView. This will hopefully provide a head start for anyone needing to integrate QlikView with another system via XML or web services.

It is a very simple application based around the concept of a distributed budgeting system:
- Budget data is loaded into the app from an Access database
- Users can edit the budget data in a QlikView table (using the standard INPUTFIELD functionality)
- Users then click on a button to write the updated data back to the source database.

It uses a couple of macros to convert the QlikView table to XML, and then can either save the XML or submit it to a web service for processing. To complete the proof of concept I wrote a simple "Classic ASP" page that will use the XML to update the source database and return the results of processing (including any errors) back to QlikView. But you could integrate with pretty much any application by writing the necessary web service or ETL to pick up the XML.

It’s all pretty well documented. I hope someone finds it useful!

Please note that this is an example application only, and is simply an illustration of the capabilities of macros within QlikView and the ability to "Write back" to the data source. It is not supported in any way and is offered "AS IS" for illustration purposes only.

Labels (1)
Attachments
Comments
Employee
Employee

.

0 Likes
Not applicable

This capability has many uses. We have two clients that want to analyse sales performance and identify those customers to target based on criteria set in QlikView. They wish to integrate with a CRM to provide management of that activity. Has anyone done this? PS I am aware of the SalesForce integration but wondering whether anyone has used other CRM's?

0 Likes
Partner
Partner

Hi Lee,

I need to generate XML and found your doc to be a great way to start.

There's just one part missing and unfortunately VBA isn't my thing: I'd like to group data.

Example:

LOAD * INLINE [

    Namex, Sexe

    Pete , M

    Joan, F

    Karl, M

    Gustav, M

    Roberto, M

    Ann, F

];

What I would like to achieve is something like this:

<Friends>

  <Sexe Name="M">

  <Namex Name="Pete" />

  <Namex Name="Karl" />

  <Namex Name="Gustav" />

  <Namex Name="Roberto" />

  </Sexe>

  <Sexe Name="F">

  <Namex Name="Joan" />

  <Namex Name="Ann" />

  </Sexe>

</Friends>

So my question is how to loop through a specific field, no need for a generic solution because it's one Macro and I know the fieldnames. Would you be so kind?

Thanks in advance..

Thanks anyway 🙂

0 Likes
Employee
Employee

Hi Fred

Sorry, I don't have the bandwidth to do this. I would suggest a web developer or even googling for some sample code. There are some great examples on w3schools site, like the following:

http://www.w3schools.com/dom/dom_nodes_traverse.asp

Partner
Partner

Hi Lee,

You did a great job already. Thanks.

0 Likes
Partner
Partner

Thank you! Just what I needed.

A few minor XML-bugs, that's all.

If case anyone else needs this, here's the fixes:

Unless your output is UTF-8, you should include the encoding (in double quotes):

sXML = "<?xml version=""1.0"" encoding=""ISO-8859-1""?>" + vbCrLf
sXML = sXML + "<data table='" + sTableID + "'>" + vbCrLf

And for the value, you should escape the & character (possibly also < and > if you have them in your data).

sValue = Replace(Replace(Replace(oTableBox.GetCell(iRow,iCol).Text, "&", "&amp;"), "<", "&lt;"), ">", "&gt;")

Also, if your columns contain spaces or other funny characters, you'll have to sort that out too.

Thanks for the code, Lee!

Vegard

0 Likes
jerifortune
Contributor II

Thanks Lee.

I am looking for similar implementation using QlikSense. 

0 Likes
Employee
Employee

I would first look at Qlik Branch. There are a handful of free solutions for write back commentary, but none I am aware of for a tabular version. You could write your own, or there are commercial solutions like Jedox available.

0 Likes
jerifortune
Contributor II

Thank you. I will explore the options.

0 Likes
Partner
Partner

Hi Jerry, take a look at LLodi, I think it may be what you are looking for.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2012-01-17 08:49 PM
Updated by:
Employee