Posting Data from QlikView to Other Systems using the Ajax Client

There are many scenarios where it is necessary to process QlikView data with other systems. There are many approaches and examples available how this can be achieved using Macros (based on the QlikView IE-Plugin) but this article cover how you can realize such a requirement by using only the QlikView Ajax client.

Scenarios

Let’s first talk about some of the scenarios where passing data from QlikView to other systems is necessary. Just some sample requirements:

  • Export to a relational database management system
  • Creating Excel sheets (with more logic than the standard Excel export of QlikView provides)
  • Processing QlikView data with other system
  • Exporting QlikView data to cloud system like salesforce.com, Amazon, etc.

The Web Based Approach Using QlikView Extensions

Sure, you can realize most of the scenarios above using macros and the QlikView IE-Plugin …

But my intention was to create a prototype to demonstrate an approach which can be used for all scenarios with as less as possible modifications.

Data2Webservice_Extensions
Some explanations:

  • I have created a QlikView object extension which displays dimensions and expressions and offers the possibility to select data
  • Then there is a button within that extension which triggers the process of posting all data displayed within the extension to a web service. The Url of that web service can be defined within the extension.
  • The referenced web service receives these data and is capable processing the data to any system which offers APIs (this requires manual coding depending on the desired target system)

Data2Webservice Object Extension – Step by Step:

Follow these steps to get the Data2Webservice object extension to work:

image

1) Installation and preparation
Installing and dragging the Data2Webservice object extension onto your QlikView document is straightforward as described in this article. In addition you have to prepare the web service which can be referenced by the extension.
(As a starting point you can use the sample web service provided with the code of this article)

2) Define Dimensions and Expressions
Define the desired dimensions and expressions as if you were using a standard QlikView object:

image

 

3) Additional Extension properties
Finally set some properties:

image

A Sample Web Service:

I have created a sample web service which can be used as a starting point for working with data posted by the Data2Webservice extension.

First of all I have created some classes (in C#/.net) which should make it easier to work with the passed data table in a structured way. The screenshot below displays the class hierarchy within the sample web service:

image

Using this classes makes it easy to process the data as demonstrated below:

image

Improvements

Since this is just a prototype there are a lot of improvements which I could think of:

  • Enhance the experience of the table (fixed column headers, etc.)
  • Provide samples for server side processing (Excel generation, saving to a database, etc.)
  • Better handling of web service return codes (in case of success AND failure)

Tested with …

I have tested the extension using:

  • QlikView 11 SR2 (x64), both using the QlikView Desktop Client and QlikView Server
  • Internet Explorer 9
  • Chrome 22
  • Firefox 15

Download

You’ll find the source code for both the extension and a sample webservice on GitHub:

https://github.com/stefanwalther/QlikView_Extension_Data2Webservice

Bookmark and Share

and tagged , . Bookmark the permalink. Both comments and trackbacks are currently closed.

32 Comments

  1. Posted November 13, 2012 at 21:59 | Permalink

    nice one :)

  2. kaj
    Posted December 7, 2012 at 13:51 | Permalink

    Will this extension object work on QlikView Personal Edition?
    Do we have stepwise document for the above process? Can you please share that ?
    Thanks in advance for the help :)

    • Posted December 25, 2012 at 21:38 | Permalink

      Theoretically this extension will also work in QlikView Personal Edition (if you turn WebView on) but this is absolutely not the intention of this extension (but you will still need a web service the extension can communicate with and send the data to).

      The idea of this extension is to offer the above explained functionality to business users using the Ajax-/Web-Client via QlikView Accesspoint ….

      Regards
      Stefan

  3. Posted December 27, 2012 at 16:26 | Permalink

    Hi Stefan,

    Excellent article, this will save a lot of time!

    Cheers,
    Barry

  4. Posted January 20, 2013 at 02:02 | Permalink
  5. Lee Matthews
    Posted March 7, 2013 at 02:45 | Permalink

    Hi Stefan

    This is very cool. How much work would be required at the web service end to insert into a SQL Server table for example, assuming that there was a 1:1 match between the fields submitted and the columns in the SQL table?

    • Posted March 11, 2013 at 22:38 | Permalink

      Hi Lee,

      not really much work … I have already received this request several times … So I hope I will be able to post a simple sample very soon 😉

      Regards
      Stefan

      • Ratan
        Posted November 29, 2013 at 11:42 | Permalink

        Hi , Do u Resolve it . If yes , Please Post it .

  6. Parul Jain
    Posted March 27, 2013 at 17:07 | Permalink

    Hi Stefan,

    Greatly appreciate you sharing your insight and code! I was wondering if you have thought about bringing changes to the data back in the dashboard in near real time? QlikTech is not confident in their Real Time Server offering. Incremental loads triggered by clock (periodic) or EDX could be an answer. However with many simultaneous users EDX is probably not right, and users may have to wait too long for the next clock based incremental reload.

    My specific use case is to change one attribute and have that reflected back. To illustrate, let me try to draw a parallel with email messages. Once the message is read, I want the user to click on the Read button that sets the Read field to “Yes”. The user can then choose to see Read and Unread messages by clicking Yes or No in the Read list box.

    Many thanks,
    Parul

    • Posted March 28, 2013 at 01:33 | Permalink

      Hi Parul,

      what’s about using Direct Discovery? Wouldn’t that exactly solve your issues?

      Regards
      Stefan

  7. Sven
    Posted April 24, 2013 at 17:18 | Permalink

    Hi Stefan,
    do you know, if it’s possible to run the backend-service directly within the qlikview-webserver?

    Many thanks,
    Sven

    • Posted April 24, 2013 at 17:19 | Permalink

      No, using only QVWS is not enough, you’ll need a Webserver like IIS

      Regards
      Stefan

  8. Arnold Ayaoan
    Posted May 29, 2013 at 09:53 | Permalink

    Hi Stefan,

    Really appreciate the generosity sharing this extension this saves me a lot of time.
    Could you please help give me some idea how to retrieve the data of other QlikView objects from my extension similar to this?

    Thank you.

    Regards,
    Arnold

    • Posted June 6, 2013 at 00:09 | Permalink

      Hi,

      have a look in the JavaScript API.
      Some simple example:

      var objListBox = qva.GetQvObject("Document\\LB01" , function () {
         // do something here ...
      });
      

      Regards
      Stefan

      • Arnold Ayaoan
        Posted June 20, 2013 at 11:47 | Permalink

        Hi Stefan,

        I really appreciate the help. I’m just wondering you are also familiar how to get the current selection in a document. Below is my code:

        doc = Qv.GetCurrentDocument();
        var fieldOptions1 = {
        “name”: “Outlet ID”
        };
        var fieldOptions2 = {
        “name”: “Outlet Name”
        };

        var currentSelectionOptions =
        {
        onChange: function() {

        $(‘#divSelection’).empty();

        var data = this.Data.Rows;

        for (var f = 0; f < data.length; f++) {

        var field = data[f];
        var name = field[0].text;
        var vals = field[2].text;

        $('#divSelection').append(name + ":" + vals);
        }
        },
        fields: [fieldOptions1, fieldOptions2]
        };
        doc.GetCurrentSelections(currentSelectionOptions);

        Thank you.

        Regards,
        Arnold

  9. Michael Heiler
    Posted June 17, 2013 at 22:02 | Permalink

    Thanks Stefan. Great article.

    This all works well when I run it locally with the Desktop client but once deployed to the Access Point I receive access denied when accessing the Web Service.

    Any ideas what that could be ?

    thanks
    Michael

    • Manjushree Nimbalkar
      Posted June 20, 2013 at 21:58 | Permalink

      Hello Michael,

      Were you able to solve this issue? I’m also getting access denied error which accessing the Web service.

      Thanks in advance,
      Manjushree

  10. Manjushree Nimbalkar
    Posted June 18, 2013 at 23:50 | Permalink

    Hello Stefan,

    Nice article. I created webservice and used the extension. Mentioned the dimensions and measurement but don’t see data loaded in the chart. Will you please tell me what am I missing here?

    Thanks,
    Manjushree

  11. Manjushree Nimbalkar
    Posted June 18, 2013 at 23:58 | Permalink

    Hello,

    Anyone who faced this issue please let me know how it can be fixed.
    Thanks in advance for the help.

    Manjushree

  12. Michael Heiler
    Posted June 20, 2013 at 22:03 | Permalink

    Yes, I solved it. It most likely has to do with IE security settings. Try changing the settings to allow data from different sources.

    Michael

  13. Posted October 1, 2013 at 17:19 | Permalink

    Hi Stefan,

    Excellent Article, I found it very useful.

    I am currently looking at a integrating WCF with QlikView.

    Thanks

    Mark

  14. Stefan Stoichev
    Posted October 24, 2013 at 04:25 | Permalink

    Hi Stefan,

    is the extension will work with input fields?

    Thanks!
    Stefan

    • Posted November 18, 2013 at 19:21 | Permalink

      Hi Stefan,

      to be honest, I have not tested this so far, but I cannot imagine how this should work …
      But instead of testing this scenario (input fields) I will publish soon a set of extensions which are meant as a replacement for adding comments on a per-line basis …

      Regards
      Stefan

  15. Supriyo Dawn
    Posted December 18, 2013 at 10:35 | Permalink

    Dear Stefan,
    This blog is very useful. I installed Data2Webservice extension object in a desktop machine, in which QlikView Version 11.20.12129.0 SR4 64-bit Edition (x64) installed. I am facing a problem regarding data and button not showing in Data2Webservice object under webview mode. I am requesting to sort out this problem as soon as possible.

    Thanks in advance
    Supriyo

  16. Allen
    Posted January 28, 2014 at 21:56 | Permalink

    I am having same issue regarding this working on my laptop and on a server, however, when posted to the access point, I do not see any data in the object.
    I can actually export data to excel, it just doesn’t show in my browser.
    I can also change the Title Bar on the Extension Object which updates correctly.
    just no data showing..
    any help would be appreciated…

  17. Ben
    Posted March 11, 2014 at 15:10 | Permalink

    Hi !

    Thanks for your article, this is really interesting. I’m working on Qlikview but I dont find how to read your file swr.QlikView.Extensions.DataSelection.WebService.csproj.

    I have visual studio 2012 and 2013, and it’s not working when i’m opening it.

    I’m used to work in JEE, so if you just can help me to open it, that would be great !

  18. Dave
    Posted March 14, 2014 at 21:22 | Permalink

    Hi Stefan,

    Thank you for posting this great article along with your source code. I’m new to the QlikView world and your site has given me a great intro to what’s possible. Your solution here is the close to meeting my requirements, except that I’d like to have the web service initiate the call in order to retrieve the data from the QlikView document. Do you know of any approaches that can accomplish this? Ideally, I’d like to host a REST service on the QlikView server which can accept a document name along with some criteria and return back JSON data. An initial thought would be to somehow integrate your solution on loading of the QlikView document. I’d appreciate any help.

    Best Regards,
    Dave

  19. james
    Posted March 22, 2014 at 23:28 | Permalink

    Hi Stefan,
    I followed your example but it is keep throwing this error msg

    Error posting the data to the
    Error Status

    what I am missing?, your ext work fine no issue but on submit it is giving this error

    and error does not say any thing it is just ur javascript alert

  20. QlikQuickView
    Posted May 6, 2014 at 20:03 | Permalink

    Hi Stefan,

    I would like to know how to install/set up the web service from your example.

    Thanks in advance,
    Regards.

  21. Márcio Campestrini
    Posted June 3, 2014 at 20:27 | Permalink

    Stefan,

    Thanks a lot. We have a team that can develop the webservices and your extension will solve too many issues that we have.

  22. Brindlogcool
    Posted June 11, 2014 at 15:26 | Permalink

    Hi Stefan,
    I tried to use the extension and I am getting the error as

    Error Posting the data to web service
    Error Status: 0
    Error Status: error

    How to configure the web service url in the extension
    Do I need to place the web service code in the IIS server?