If asked what data sources QlikView will load from, I will say almost any data source. It’s the “almost” which is a bit irksome – but there are ways of loading from even more sources than are available out of the box.
The Secure Web Portal
One of my clients had the requirement to load data from a secure CMS which requires users to log in to the portal using a web form and then authenticates against a cookie. Without this cookie no data is returned. This is a relatively common way of securing web sites, and data on these sites is one of those sources that QlikView can’t read from natively.
The client had made a few attempts at getting at this data, using batch files and command line parameters to browsers – but none of these attempts had allowed them to returns the data automatically.
The Third Party Solution
When this was put to us, it immediately felt like something that QVSource would be able to deal with. The connector which communicates with Google Drive works brilliantly, allowing QlikView to load directly from Google Spreadsheets stored on-line. With a bit of use of Chrome’s developer tools we were able to get the cookie from the site we were connecting to and a copy paste this into the QVSource GUI. This allowed us to load the data we required. However, we were still unable to get to a fully automated solution that would deal with cookies expiring.
One of the things frequently cited about QVSource in reviews is the incredible level of support given by Darren and Chris at Industrial CodeBox – the developers of QVSource. Now seemed like a good time to call on this support once more. Chris confirmed we were pointing in the right direction, but there was no way to grab a cookie from a web request and then push that back into a subsequent call. It was looking like a return to the drawing board was required. However, Chris said he would take a further look at it.
By the next morning there was a new build of QVSource ready for us to try with the cookie transfer feature built in. Not only that, Chris also provided an example script showing exactly what we needed to do to get the data we required from the site we were attempting to access.
You simply couldn’t ask for better service.
For a connection like the one we were attempting to make, you first need to make an XML call with credentials for the site. From build 220.127.116.11 of QVSource onwards you can then extract that cookie from the XML results. This is then passed into the URL as a parameter for the final call. The UI for QVSource allows you to copy and paste in the target URL and the required parameters and it returns the modified URL which can be passed directly into QlikView.
Examples of how to connect to many different sources, including secured web sites, are available on the QVSource wiki here: http://wiki.qvsource.com/. If you are ever presented with a data source you are unsure how to work with then you should definitely look at QVSource as a route to make that load work.
Many thanks to Chris Brain and Darren Ball for their continued work making QlikView the very best connected business discovery tool out there, and for their outstanding support in this instance.
UPDATE: QVSource is now an official Qlik product – Qlik Web Connectors.