Qlik Community

QlikView Documents

Documents for QlikView related information.

How to call a WebService to pull some data into the QV application

Partner
Partner

How to call a WebService to pull some data into the QV application

Note: This document was originally written by Rakesh Mehta, I've just cleaned it up a bit...

Hi Guys, I saw this question asked many times in the forums so thought I would do a little write up on it so all can take little benifit of the trick.

In my example I will use a freely available webservice from http://www.cdyne.com/ called - weather forecast webservice. The base URL for this webservice is http://wsf.cdyne.com/WeatherWS/Weather.asmx. The method I will use in my explaination is "GetCityWeatherByZIP" method. This method returns weather forecast of next 7 days for a given US zip code.

We all know that one way to get data consuming a webservice is by building a Custom Data DLL interface (eg salesforce example from QlikTech), which werks perfectly but surely requires extra programming abilities like in C++, etc.

What I have found that a webservice can also be consumed (or invoked) using 3 ways:

  • A POST command that embeds a SOAP request
  • A POST command that specifies the method name and parameters
  • A GET command whose URL contains the method name and parameters

In my example, I have used the third method - A GET command whose URL contains the method name and parameters. Below is how I programmed it:

  1. Call the URL http://wsf.cdyne.com/WeatherWS/Weather.asmx?op=GetCityWeatherByZIP in Internet Explorer, and put a valid US zip code. Press "Invoke" button. This will return you an XML file with weather forecast of that zip code of next 7 days. Copy all the xml data (CNTL-A + Copy) and Paste it in a notepad session. Save this file as temp.xml somewhere on your PC.
  2. Open a new QVW file, and in the script editor press "XLM Files" button. Locate the file you saved in step 1 and go through the wizard. This will put a script to load the data. Go ahead and remove the path from the file name, leaving just the file name. By doing 1 & 2 steps, now you have a valid load statement with correct table and field names.
  3. Open VB Macro script editor and Place this script:

    Sub ws
    Const HOST = "http://wsf.cdyne.com/WeatherWS/"
    Const URL = "Weather.asmx"

    'Create xmlhttp opject
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

    'Get the selected zip code
    set fld=ActiveDocument.GetField("ZipCode")

    'If user has selected more than one zip code, show error
    if fld.GetValueCount(1) <> 1 then
    msgbox ("Please select only one Zip code")
    exit sub
    end if

    'Finally, get the selected zip code value
    set val=ActiveDocument.Fields("ZipCode").GetPossibleValues
    zip = trim(val.Item(0).Text)

    'Call HTTP Get method passing WS URL and parameters
    xmlhttp.open "GET", _
    HOST & URL & "/GetCityForecastByZIP?ZIP=" & zip, false

    xmlhttp.send ""

    'Create a temporary file to save the results from WS
    set Path = ActiveDocument.Variables("vPath")
    FileName = Path.GetContent.String & "XMLOutput.xml"
    set fso = CreateObject("Scripting.FileSystemObject")
    set s = fso.CreateTextFile(FileName, True)
    s.writeline(xmlhttp.responseText)
    s.Close()

    'Load document with the temp file
    ActiveDocument.PartialReload

    'Delete the temp file
    fso.DeleteFile(FileName)
    end sub

  4. Now you need to make a way to load all possible US zip codes in the document so the user can select a zip code. I have loaded list of zip code from a URL I found while googling - http://www.census.gov/tiger/tms/gazetteer/zips.txt . You can always put an input box and let user type a zip code, and then move this zip code in a variable and then use that variable in the VBScript to pass to the WS URL.That's it. Try it with the example QVW file I have put in "Share QlikViews" section. Before you press button to get data, please make sure to give proper system access in the VB script section for macros to run through.

Now, would I use this method for importing lots of data from the internet? May be not. For that I would rather build a custom DLL. But to get little things, like current stock quote, or some currency exchange rates, etc. I think this method can be utilized.

Anyway, something to share. Have fun! Comments / suggestions are welcome.

Rakesh Mehta

Labels (2)
Attachments
Comments
MVP & Luminary
MVP & Luminary
Rakesh, Excellent write up! I found it very useful. I made some improvements to your example qvw and uploaded to Share Qlikviews / How-to here: http://community.qlik.com/media/p/63030.aspx The updates are documented in the "Change Log" sheet in the qvw. -Rob
0 Likes
Partner
Partner
Thanks Rob. Sometime we forget to use what we suggest to others. 🙂 http://community.qlik.com/forums/p/16177/62939.aspx#62939 Glad to see some feedback. -Rakesh
0 Likes
Not applicable
Excellent walkthrough! Good job! /Fredrik
0 Likes
Partner
Partner
Very good. But it shouldn't be forgetten to allow System Access in Macro module, otherwise you receive "permission denied" error.
0 Likes
johnan
Contributor III
Hi, tested and it works fine, but, how do i get this to work in QV server? I have publish this and it seems that qlikview can not find the XML file?
0 Likes
jasonseril
Contributor
Hello Rakesh. this is a wonderful webservice sample application. We have a requirement (via ie plugin) to retrieve data from a webservice and display it directly to chart objects. because i think we can't open your sample application through an ie plugin, "Error: ActiveX component can't create objct...", our plan is to pull and extract the xml data using regular expression then directly input those data to chart objects. I have created such application and i'm stuck on directly displaying parsed xml data to chart objects. I uploaded the sample file in this thread --> http://community.qlik.com/forums/t/26841.aspx Please help, thanks in advance. regards, jason
0 Likes
Partner
Partner

Hey Rakesh, good job. It looks great!

Otherwise, I don't know why, it doesn't work. When I try to exceute the button "Get Weather", the macro gets stuck in :

    xmlhttp.send ""

Anybody knows why?

PS: I have "Allow System Access" in the module security to access to internet. in the module security.

Many thanks in advance!!  

0 Likes
Not applicable

It fails for me, and says " Duplicate keys when loading Input Field" I'm using qv 11 beta. Any ideas???

Alex

kve_credon
New Contributor

Gents,
Just downloaded this example, but for some reason, it doesn't seem to work for me.
Same error as post from marcel.olmo1 (two line above)
Could this be related to the fact that we are working in QV10 ? (your qvw is probably tested in v8 or 9)

0 Likes
kve_credon
New Contributor

Rob,
I know you are very experienced in QV. Could you maybe point me forward ?
Need this functionality, to extract data from a webbased Accounting software...


Cheers,
Kurt

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2009-04-27 01:49 AM
Updated by: