Using QlikView with Web Services

by Stefan Stoichev on September 20, 2010

Among other data sources these days web services became main way to expose data over internet.  In this post will show you how to consume and load data from web services.

First of all I separate web services in two categories depending on return format and depending on if service need or don’t need to post data.

Without posting This is the simplest  way to load data from web service.

Example of this scenario is to load data from Twitter search for specific term. According to the Twitter API documentation the requested link will be http://search.twitter.com/search.atom?&q=qlikview . As you can see the search term/parameter (qlikview) is embedded in URL itself. Loading data from this type of services is easy from Edit Script: Specify loading for web files Enter the web file URL Press Next and here it is the raw data ready for load With posting

In a previous example all parameters are in URL itself but other “type” of web services require parameters or other data to be send to the web service before returning data. Loading web files through wizard cannot achieve this. So this solution will lay on VBScript macro to do the job. The script bellow will call the web service, post necessary data/parameters, get the response and save it in xml file. function

GetWebServiceData()

' Define the URL of the service
url = "http://mywebservicesite.com/projets.xml"
' Define parameters to post (in this case will return data for all projects)
request = "project_id=allprojects"
Set objHTTP = Createobject("MSXML2.ServerXMLHTTP")
'Open channel
objHTTP.open "POST", url, False
' Send header
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
' Send parameters
objHTTP.send request
' Get responce xml in string
responce = objHTTP.responseText
Set objHTTP = Nothing
' At this point web service responded and gave back the response xml (if there is no error)
' Common case is to return errors in xml format
' Since the returned xml is in the memory we will need to store it in local file
' Set folder name where file will be save
foldername = "C:\ProjectFiles"
' Set file name where data will be stored
filename = "xmlReponce.xml"
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Check if folder exists else create it
If objFSO.FolderExists(foldername) Then
Set objFolder = objFSO.GetFolder(foldername)
Else
Set objFolder = objFSO.CreateFolder(foldername)
End If
' Check if file exists else create it
If objFSO.FileExists(foldername & filename) Then
Set objFolder = objFSO.GetFolder(foldername)
Else
Set objFile = objFSO.CreateTextFile(foldername & filename)
End If
set objFile = nothing
set objFolder = nothing
' Constant that indicate that file content will be overwritten and not appended
Const ForAppending = 2
' Open the text file
Set objTextFile = objFSO.OpenTextFile (foldername & filename, ForAppending, True)
' And paste the content
objTextFile.WriteLine(responce )
objTextFile.Close
end function

To call this function in reload just LET script variable in the script like follows: let LoadData = GetWebServiceData(); Such call may be done in any part of the script and depend on your needs. And when the file is reloaded the function will be called and file(s) will be downloaded.

Return format

In most cases web services return data in two formats – XML and JSON. QlikView natively can load data from XML files, but for JSON there is no simple way to load the data. The solution is to find (or write your own) a web service that accepts as parameter JSON structured file/string and return XML file.

One disadvantage is that internet connection is needed. Another disadvantage is that if  you are not the owner of the service and try to convert big amount of data this may cause a trouble.

A few days ago I found a dll that can convert JSON to XML (you can find it here). I wrote another dll that uses json.net dll which accepts JSON text and returns converted XML string or saves the result to specified file.

The script bellow shows how to call the dll and use its methods in VBScript:

function ConvertJSONToXML()
Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")

'In this case read the JSON from text file
Set objTextFile = objFSO.OpenTextFile("c:\scripts\json.txt", ForReading)

strText = objTextFile.ReadAll
objTextFile.Close

'Call the dll
Set JsonToXML= CreateObject("JsonToXMLQV.JsonToXML")
' The dll has two return methods
' Return the converted xml directly to specified file
xmlText = JsonToXML.ReturnXMLToFile(strText, "c:\scripts\xmlOutputFile.xml")

' And the other method is to return xml as string '
xmlText = JsonToXML.ReturnXML(strText)
end function

If you are interested in dll convert you can download it from here JsonToXML 32-bit and JsonToXML 64-bit.

After download it runs the following command in command prompt to register it (.net 2.o framework is required)

“c:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe” “c:\PATH_TO_DLL\JsonToXML.dll” /codebase (for 64-bit version: “c:\Windows\Microsoft.NET\Framework64\v2.0.50727\RegAsm.exe” “c:\PATH_TO_DLL\JsonToXML.dll” /codebase)

If you have trouble with dll registration try to run command prompt as administrator (Start –> Programs –> Accessories  –> Right click on Command Prompt –> Run as administrator)

Stefan

P.S. Don’t forget to set macro module security on System Access/Allow System Access

{ 32 comments… read them below or add one }

1 Pinon September 29, 2010 at 05:38

Thank you for a nice post.

Quick question: will this run in QlikView Ajax client, specially the “with posting”? Thanks!

Reply

2 Stefan Stoichev September 29, 2010 at 10:03

Hi Pinon,
i just test it on QV server and it works but you must check “Allow Unsafe Macro Execution on Server” and “Allow Macro Execution on Server” in QlikView Management Console –> Qlikview Server Settings –> Security Tab

Reply

3 Rajeev June 29, 2011 at 18:09

Thanks for the information and the dll. It is working correctly when I allow system access in the macro editor.

But when the JSON’s root element has multiple properties, Json.NET dll throws this error: “JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document. Consider specifing a DeserializeRootElementName.”

Json.NET documentation says this: “Because valid XML must have one root element the JSON passed to DeserializeXmlNode should have one property in the root JSON object. If the root JSON object has multiple properties then the overload that also takes an element name should be used. A root element with that name will be inserted into the deserialized XmlNode.”

So would you be so kind to either share the source of your dll or update it so that it can handle this kind of JSON?

Thanks a lot!

Reply

4 Stefan Stoichev July 1, 2011 at 18:35

Hi Rajeev,

i’ve updated both dll’s so now if the JSON text contain multiple roots additional node will be created in the result xml (“root”). After downloading and registering the dll don’t forget to restart QlikView for changes to take effect.

Stefan

Reply

5 Chris July 16, 2011 at 12:10

You can now also load data into QlikView from Twitter (as well as Facebook, LinkedIn and many other sources) using QVSource (http://www.qvsource.com/).

Reply

6 Srini August 24, 2011 at 16:19

Hi Stefan,

I followed all your steps. Still i face a problem.
Actually the DLL is registered and added macro inside QV.
But when i trigger the macro, it is creating an XML file but, it is blank.
This is the data present in the json.txt [
Film
Jurassic Park
Zodiac
Godfather
Alice in Wonderland]

Can you help on this?.

Reply

7 Stefan Stoichev August 24, 2011 at 16:26

Hi Srini,

can you send me the json file that you try to convert to stefan.stoichev@gmail.com so i can test it locally?

Thanks!
Stefan

Reply

8 Srini August 31, 2011 at 11:17

Thanks Stefan.
I tried with our DLL, we face an error. “ActiveX component can’t create object”.
By the way what do you pass in CreateObject() function.
And can you give the JsonToXML.dll Source code.
We tried by passing dllfilename.function.

Thanks & Regards
Srini.

Reply

9 Stefan Stoichev August 31, 2011 at 11:42

Hi Srini,
did you change the Requested module security to System Access and Current Local Security to Allow System Access in Macro Module?

Stefan

Reply

10 Srini August 31, 2011 at 15:07

Hi Stefan,
Thanks for your help.
Finally we solved the probelm and thanks for your valuable suggestion.

It was because of the property present in the source code. We changed it and we were able to register and call the DLL.

Thanks & Regards,
Srini.

11 Gustav Petersson September 10, 2011 at 17:35

Hi Stefan!
I really appreciate your tip. The script is very handy to pull some external data. I’m quite new to the whole QlikView and especially macros in QlikView. I tried to modify the macro to accept an argument to put in the webbservice request.
My webbservice request prefers GET-requests, so I put the search-string in the url.

Changing the first rows of the macro like this:

function GetWebServiceData(freetext)
‘ Define the URL of the service
url = “http://myip.com/search?q=” + freetext
….
‘ Send parameters
objHTTP.send request


AND changing the load-script to:
LET LoadData = GetWebServiceData(‘Andy’);

Still it seems like I’m not able to pass on the argument ‘Andy’ to the function? Do you know how I could go on and troubleshoot this? Any more tips on how to pass on an argument to the macro?

/Gustav

Reply

12 Stefan Stoichev September 10, 2011 at 22:03

Hi Gustav

welcome aboard :)

i think that there is no need to use the macro for getting data from web services using GET request. “Without posting” part will do the job. When QV generate the load script you can change it to use parameters for your query. Something like this:

SET LoadData = ‘Andy’;

LOAD
//Your fields here
FROM
[http://myip.com/search?q=$(LoadData)]
(txt, codepage is 1252, embedded labels, delimiter is ‘\t’, msq);

But anyway to use the macro function with parameter and GET request rows above foldername = “C:\ProjectFiles” should be replaced with:

function GetWebServiceData(searchString)
Set objHTTP = CreateObject(“MSXML2.XMLHTTP”)
objHTTP.open “GET”, “http://myip.com/search?q=” & searchString, False
objHTTP.send
responce = objHTTP.responseText

‘ Rest of the function that saves the response to file
end function

Then calling the function with:
LET LoadData = GetWebServiceData(‘Andy’);
should work.

If you have any other questions fell free to post it or mail me.

Stefan

Reply

13 Alexandre February 28, 2012 at 16:28

Hi Stefan, thanks for your post!
My webservice needs a login credential. Do you know how i can access it from Qlikview?

Thanks

Alexandre

Reply

14 Stefan Stoichev February 29, 2012 at 14:37

Hi Alexandre,

without testing it i think you can try:
‘Open channel
objHTTP.open “POST”, url, False, “username”, “password”

Stefan

Reply

15 Maurício March 20, 2012 at 21:40

Hi Stefan,

I do not know how to do in QoL what a client is in java:

He goes to a URL (http://172.17.34.248:9080/WsConsultaAudienciaIbopeService/WsConsultaAudienciaIbopePort) xml below to retrieve the xml data.
You know how I can do?

thank you



1

2012-01-01T00:00:00

10

Reply

16 Chris June 23, 2012 at 10:36

We now have a solution for connecting to any XML/JSON API from QVSource:
http://www.qvsource.com/wiki/General-Web-Connector-For-QlikView.ashx

Would be great to hear any feedback.

Reply

17 Madeline September 21, 2012 at 06:15

I’d like to tell you about a change of address cheap viagra commute form housing to practice site. When it is not possible to place students in an AHEC

Reply

18 Best Sales September 25, 2012 at 10:23

I was reading thru and find that the sites content is good and well written. Looks like alot of time and effort has been placed. Keep up the good work. i have bookmarked this website! thank you

Best Sales

Reply

19 Mary September 25, 2012 at 17:03

Other amount topamax yellow pill knowledge and skills acquired through didactic education and Introductory Pharmacy Practice Experiences and apply them in direct

Reply

20 Ashley October 2, 2012 at 19:10

How would you like the money? order retin a .05 cream ” Section One may be one of the following:

Reply

21 Miguel October 3, 2012 at 11:36

Could I have a statement, please? seroquel prices pharmacies Contact information and hours of operation

Reply

22 Hunter October 4, 2012 at 05:43

What sort of work do you do? abilify 30 mg tablets Pharmacy law Emerging Proficiency

Reply

23 Connor October 4, 2012 at 10:35

Go travelling amitriptyline 300mg 66124070 Methadone pain 20mg/ml

Reply

24 a459933 October 5, 2012 at 01:04

I’ve said that least 459933 times. SCK was here

Reply

25 china jewelry wholesale October 5, 2012 at 04:28

I’ve been thinking the very same idea myself recently. Delighted to see someone on the same wavelength! Nice article.

Reply

26 Wholesale brooch October 5, 2012 at 08:36

You are so intelligent. You realize therefore considerably with regards to this matter, produced me individually believe it from numerous numerous angles. Its like women and men don’t seem to be interested unless it is one thing to accomplish with Lady gaga!

Reply

27 nikon lenses October 5, 2012 at 09:01

Subsequently, following spending several hours on the internet at past We’ve uncovered anyone that definitely does know what they’re discussing thank you very significantly amazing blog post

Reply

28 James October 5, 2012 at 11:25

Where are you calling from? purchase trazodone online the back of the prescription.

Reply

29 Stefan Walther November 10, 2012 at 11:17

Hi,

also have a look at the following extension I have created for calling webservices using a QlikView object extension:

http://www.qlikblog.at/1798/posting-data-from-qlikview-to-other-systems-using-the-ajax-client-and-extensions/

Regards
Stefan

Reply

30 motorcycle jackets For Women March 23, 2013 at 03:53

Fine way of telling, and nice article to
obtain facts about my presentation focus, which i am going to present in college.

Reply

31 gur June 10, 2013 at 15:37

Hi,

Is there a way to “consume” QV data as a webservice ?
For example – having a dashboard like “Geckoboard” which will pull out data from QV ?

Thanks

Reply

32 you April 19, 2014 at 19:12

We stumbled over here coming from a different web page and thought I
may as well check things out. I like what I see so i am just following you.

Look forward to looking over your web page
yet again.

Reply

Leave a Comment

Previous post:

Next post: