QlikView and R Integration for Predictive Analytics Example

[Brian_Bauer]

If you have never thought about integrating predictive analytics and QlikView, may this article pique your interest on the topic.  If you have thought about the potential, but do not know how to get started, may this be the motivation to start your journey.

This demo example illustrates the art of the possible around the interoperability of the R statistics engine and QlikView 11 for enabling predictive algorithms within QlikView today.

R (http://www.r-project.org/) is a free software environment for statistical computing and graphics, which supports predictive modeling and scoring.  The example also introduces Rattle (Rattle: A Graphical User Interface for Data Mining using R -- http://rattle.togaware.com/).  Rattle was used to create the predictive model and generate the R code implemented within the example QlikView application.  The use of Rattle is not detailed here.  It is left up to you to explore.

The example assumes you understand R and how to create predictive models in R and how scoring works in R.  If you understand how to do those things in R and you know how QlikView works, this example should give you an idea of how:

1. you could implement a compelling QlikView application for discovering trends in data as you have become used to with QlikView
2. while adding the ability to predict outcomes using a proven, trained scoring model you have built in R
3. and while showing actionable predicted outcomes at the point of decision

-- all without the end user having to leave QlikView.

As in the previous example posted by Elif Tutuk, "Integrating QV with R example kit.zip" (http://community.qlik.com/docs/DOC-2975), the example utilizes statconnDCOM.  A slightly different approach in this example overcomes a break in functionality introduced into that earlier example by the changing in the behavior of one of the COM API methods in the most recent releases of statconnDCOM.

The integration is done using QlikView's built-in VBScript support to interoperate with the DCOM-based API.  This is just one way to integrate with R.  With a little research you can discover other potential opportunities for R integration (e.g., OpenCPU).  Also, note that R is just one such example of a statistical engine being integrated with QlikView.  Other solutions, such as commercially available statistical engines or predictive scoring engines may also be integrated in similar ways.

To get started with this example, save the attached file, QlikView-R_Predictive_Demo.zip, to your system.  Open the zip file, and copy the enclosed folder (QlikView-R_Predictive_Demo) to a location of your choice.  The location is not important as the example is portable and has no dependency on being placed in a particular folder on your system. Now navigate into the folder you just extracted and find the document named “Qlikview-R Integration Demo 20170310.pdf”.  Open the document and follow the instructions step-by-step.

UPDATE (May 7, 2014):  I updated the installation document (Qlikview-R Integration Demo 20140421.pdf) to eliminate the RAndFriends installer as it has been unavailable for some time.  This edition of the document references R 3.1.0.  Hopefully the instructions will continue to be a good guide for subsequent versions as well.  I will continue to monitor for changes in R, statconnDCOM and other R components which could effect how the demo application works going forward.  The example QlikView application itself remains unchanged. It is a Personal Edition version, so you should be able to explore it with a licensed QlikView Desktop or the Personal Edition.  Thank you for your continued feedback on this demo.

UPDATE (September 21, 2015):  Everyone who has been seeing the message "The license that is embedded in this document is not valid":  I attached an updated version of QlikView-R_Predictive_Demo.zip‌.  Ensure you overwrite the old QVW with the new one in this package.  That is the only change.  Please forgive this looooong overdue update. 

UPDATE (October 9, 2016):  Updated the embedded license to work with QlikView Desktop Personal Edition.

UPDATE (February 2, 2017):  Updated the embedded license to work with QlikView Desktop Personal Edition.

UPDATE (March 10. 2017):  A dependency for this particular example is statconnDCOM from the team at autstat (http://www.autstat.com/).  If you wish to utilize statconnDCOM, you will need to contact autstat directly and purchase a license or request an evaluation license for testing.

[#2]

I realize that this example is not designed for QlikView Server, but I am very interested in the possibility of 'self-service' predictive analytics (that is achievable by the push of a button just like this example). With that said, does anyone know what it would take to deploy this on a QlikView Server? Is it even possible?

Thank you for the input!

[#3]

Thanks it seemed to work for me. RAndFriends seems to be gone now but I was able to install the packages individually and it worked. Changing export type to ANSI seems to have been key for me and was the final piece to get it working.

Jason

[#4]

Hi Brian,

Can you please help. When I try to open the Personal Edition Demo Document, it gives an error: The license that is embedded in this document is not valid. Request you to guide.

Regards
Pankaj Raj

[Brian_Bauer]

Hello Pankaj,

Apologies for the inconvenience.  The Personal Edition (PE) version should be updated now.  Please try again and let me know it is working for you.

Regards,

-Brian

[#6]

Hey Brian,

Kindly help me out, I tried the above solution to integrate QV with 3.0.2.

When I click the Run Analysis Button in the demo, the statConn screen comes & goes away and the Macro gets opened.

Thanks in advance

[#7]

Hi Brian,

Thanks for helping but I am still not able to open the Document. It gives a series of Error, when I try to open the document. Please see the error Images. It starts with one error message, then when I click on ok, it displays 2nd error, then third and so on. And finally it opens the dashboard without any chart / data display.

Request you to guide, If I am making any mistake. Anyway thanks a lot for your response and help.

Regards

Pankaj

[Anonymous]

Hi Brian,

I followed the installation steps and integrated the solution in my environment however when trying to run macro, the statconn screen come and goes but the macro window doesn't disappeared. To test whether the R and other packages installed properly,  I copied the macro script and ran in R window and found it is working find. So there should not be any issue in the installation.

At this point of time, I can only think that statconn API not connecting to R. I found "statconnDCOM3.5-1B2_Noncommercial" not available any more so I installed "statconnDCOM3.6-0B1_Noncommercial" and I filled this could be a reason.

Let me know your view.

Thanks

SJ

[valerio_fatatis]

very good example.

most important to predict analysis

thnaks

[#10]

Thanks Brian you are superb .....  ,

I can able to connect with R 3.0.3 & application is running fine.

Just want to give my bit contribution here (maybe useful) : 

For those who is using R version 3.x or higher , apart from following steps given by Brain , need to execute below mentioned 3 commands "to sync your updated R with Statcon" :

install.packages(c("rscproxy", "rcom"), repos = "http://rcom.univie.ac.at/download", lib = .Library)

library(rcom)

comRegisterRegistry()

link for reference : http://stackoverflow.com/questions/16651837/rexcel-in-r-3-0-x

Regards, Bhushan N

[#11]

HI Brian,

Thanks a ton for the QV & R integration its of great help.

I have been able to integrate with R v2.15 but not with R v 3.0.2

When I am trying the StatConnector Test utility to do it, it gets failed.

I tried installing the rscproxy package into it but R gave the attached image as the error.

I have highlighted the error for your finding.

I checked in the 'C:\Program Files\R\R-3.0.2\library\rscproxy'. RSCPROXY was already installed with R v3.0.2.

Kindly let me know how to solve this issue.

Thanks in Advance

Swapneel