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.

[Brian_Bauer]

Hi Swapneel,

I just updated the demo package with a new document revision to the installation and configuration guide.  It is based on R 3.1.0.  Would you be able to download and follow the new instructions to remedy your rscproxy challenge?  Here is a web page you may find helpful in going to a later R version :  Uninstalling R manually --

http://homepage.univie.ac.at/erich.neuwirth/php/rcomwiki/doku.php?id=wiki:uninstalling_r_manually.

Please let me know how it works for you.  Thank you.

-Brian

[#3]

Hi Brian,

Its done    thanks a ton

Its all working very fine. I have installed R 3.1 version & that helped solve the problem

Best Regards,

Swapneel

[#4]

Hi Brian,

I followed the installation steps . When I test the conection between R and statconn I still have the same error even though I moved RCOM package

the problem that I have not found "statconnDCOM3.5-1B2_Noncommercial" so I installed "statconnDCOM3.6-0B1_Noncommercial" and I think this could be a reason.

Thanks in advance

Best Regards,

Zeineb

[#5]

Hi Zeineb,

I too have installed ""statconnDCOM3.6-0B1_Noncommercial" and its working fine with me.

Make Sure you install R 3.1 or higher version if available, as well download the "Rscproxy" package,

in short follow the entire installation pdf provided in earlier posts. And once all installation are done it should work fine.

If not, then please send us the screen shots of the errors, I hope we can help,

May be Brian may be able to help you more better.

For anything I could do, you are most welcome

Regards,

Swapneel 

[SergeyMak]

I spent some time trying to connect qlikview to R and found in file:///C:/Program%20Files%20%28x86%29/statconn/DCOM/doc/Install.html

that possible errors could be solved.

If anything goes wrong when calling .Init() of the COM object for the first time, retrieve error information using .GetErrorText():

"installation problem: unable to load connector"

R proxy library (rscproxy) could not be loaded. The cause of this common failure can be:

• The required package rscproxy has not been installed or 
• rscproxy has not been installed into the R library folder (check for %R_HOME%\library\rscproxy) or 
• the registry key corresponding to your R installation does not point to the installation folder of the R version you want to use (check HKEY_LOCAL_MACHINE\Software\R-core\R\InstallPath in the Windows registry editor) and 
• the environment variable %R_HOME% points to a directory where R is not installed and 
• rsproxy.dll and R.dll cannot be found in the %PATH% 

I just moved all libraries form my home directory to C:\Program Files\R\R-3.1.0\library

and set up the environment variable  %R_HOME% as C:\Program Files\R\R-3.1.0

then everything started to work

[#7]

if you have Issue where STATCON comes and opens the Macro window without refreshing the chart, go to "Settings->User Preferences" Export tab and set the Encoding to "ANSI".

and refresh the report.

[#8]

I got it running now, but first i also got the 'macro pop-up' error.

What I have done is, edit the macro and commented this line:

R.EvaluateNoReturn "library(colorspace)"

For some reason the colorspace package is not working properly (have to figure out why)

Thanks! (now what rests is learning R, lol)

[#9]

Hi Brian ,

I am getting an error when i reload data in the qvw file as <record> field not found , when it is clearly there in the csv files , otherwise i have installed everything as given in the manual step by step.

Can you please help?

[#10]

I am encountering the same as Ankita above. All seems to work until I open the qvw file where it gives the error <record> not found, and 2 other errors saying table not found. How to proceed?

[#11]

just figured out that using "utf8" instead of "codepage is 1252" in the source details of the table "[Readmission Scoring Info]" does eliminate the earlier errors. However when clicking the button "Run Risk Assesment" and running the macro I get the message "Cannot open file C:/...../Qlikview-R_Predictive_Demo/data/ExportFile.csv for writing!" after which QV crashes. Any idea why this happens?