article QlikView and R Integration for Predictive Analytics Example in Member Articles

QlikView and R Integration for Predictive Analytics Example

Brian_Bauer — Wed, 08 Feb 2023 11:18:53 GMT

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:

you could implement a compelling QlikView application for discovering trends in data as you have become used to with QlikView
while adding the ability to predict outcomes using a proven, trained scoring model you have built in R
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.

Re: QlikView and R Integration for Predictive Analytics Example

Tue, 02 Apr 2013 23:41:54 GMT

Hi Brian. This looks really interesting but when I get to the end, I do see the statconn splash page but also the macro window pops open again and the plots are not refreshed. Could you point me towards error logs? I'm comfortable with R and rattle but prety new to QlikView.

Many thanks,

Kendra

Re: QlikView and R Integration for Predictive Analytics Example

Brian_Bauer — Wed, 03 Apr 2013 00:28:25 GMT

Hello Kendra,

Thank you for your interest in the R integration example. A couple of questions about the version of QlikView you are using and the license you have so I can try to rule out any QlikView environmental factors.

1) Which version, release and build of QlikView 11 are you using? The current version is 11.20 SR1 build 11718.

2) Are you using the Personal Edition or have you installed a license key?

Beyond the first two questions – I am curious – did you attempt to run the scoring more than once?

Logging in my example is light. There is a simple log file (R.log) in the logs folder where you installed the app.

All R.log has in it is when the last scores were retrieved, like so: “Retrieved Scores from R at 20130402200343.” Also, the results folder should have an updated file called “ExportFile_score_idents.csv”. Both are indications that R did its thing.

And you should also have the recently generated charts from R in the plots folder.

Please let me know if the communication with R is happening properly in your environment. If the macro editor (Edit Module window) continues to pop up, I am happy to help troubleshoot further.

Best Regards,

-Brian

Re: QlikView and R Integration for Predictive Analytics Example

Wed, 03 Apr 2013 00:48:44 GMT

Hi Brian

I'm using an evaluation copy of QlikView, the version is 11.00.11282.0 SR1 and yes, I poked around for a while, opened and shut the app, ran scoring repeatedly and discovered the show-hide option (which I didn't see the first time).

There are files in the subfolders results, data etc but they all have modified dates of 27 Feb 2013 so I'm guessing that that's you and not me.

Kendra

Re: QlikView and R Integration for Predictive Analytics Example

Brian_Bauer — Wed, 03 Apr 2013 18:25:49 GMT

Hi Kendra,

Based on the files not being created, sounds like the QlikView application is not communicating with R in your environment. That would be reasonable considering the macro editor (Edit Module window) pops open for you. Likely, the VBScript in your QlikView is unable create the R COM object in the runRScore subroutine. Let's see if we can home in on the reason for why that would be the case. More questions for you:

1) Is your OS 64-bit or 32-bit? If 64-bit, do you have the 64-bit version of R (from the RAndFriends installer) and the 64-bit version of QlikView? My development and testing was done with the 64-bit versions. These questions go to trying to determine if there is a mis-match in the bit-ness of the required components. Please ensure consistency.

2) Was the copy of R and Rattle an existing version on your system? Or did you follow my instructions and install the current releases step-by-step? If you are running older versions, I suggest following my guide and installing the current releases accordingly.

To remove the QlikView release you are on as a potential reason for the integration not working on your system, I installed the same version of QlikView (11.00.11282.0 SR1) -- 64-bit -- you are using running in evaluation mode (QlikView Personal Edition). The integration with R runs normally on my system.

I await your feedback.

-Brian

Re: QlikView and R Integration for Predictive Analytics Example

Wed, 03 Apr 2013 21:48:03 GMT

Brian, in answer to your questions:

64-bit OS

I use R and Rattle already so didn't install them from the RAndFriends package. I'm running 64-bit R 2.15.3 and Rattle 2.6.26 r77.

I've just updated my version of QlikView to be the most recent 64 bit version (although still an evaluation copy).

Still no luck with the script. 😞

Kendra

Re: QlikView and R Integration for Predictive Analytics Example

Wed, 03 Apr 2013 22:09:57 GMT

Brian, looking further into this, I suspect it is something to do with a combination of statconn and the way my version of R is installed - I think the rscproxy library is not in the conventional location plus it looks like you need a paid license to run the 64-bit version of statconnDCOM.

Not to worry - at least I know it can be done! Thanks for your help.

Kendra

Re: QlikView and R Integration for Predictive Analytics Example

Tue, 09 Apr 2013 14:32:59 GMT

Works like a charm, also the older example still works on my machine.

Best regards,

Wouter

Re: QlikView and R Integration for Predictive Analytics Example

Anonymous — Thu, 20 Jun 2013 05:06:10 GMT

Hi,

We need to find out more about this integration. I try to get it working on my machine with two different QWVs, and each is working the same: I see only macro popup and that's it. I have R installed. So, there definitely need to be better instruction how to connect these two... I am using latest client, 64bit.

Regards,

Piotr

Re: QlikView and R Integration for Predictive Analytics Example

Thu, 20 Jun 2013 15:17:53 GMT

Nice post!

I am new to Qlikview but familiar with R, wonding if anyone could help with this.

Does the R integration work with Qlikview server?

Are there any licensing issues if using the statconn in a commercial setting?

Re: QlikView and R Integration for Predictive Analytics Example

Sun, 23 Jun 2013 07:16:29 GMT

Dear Moderator,

Plz upload PE enabled QV file.

Re: QlikView and R Integration for Predictive Analytics Example

Brian_Bauer — Tue, 25 Jun 2013 14:36:10 GMT

Hello Piotr,

Thank you for downloading the demo. Kendra, back in April, conveyed similar behavior. Recently I was able to see the behavior firsthand. I believe I understand what is going on. The underlying cause, in my case, was not,in actuality, that the staconnDCOM API was not interoperating with R. Rather, it was a matter with the encoding of the data file in my particular demo. If the statconnDCOM API is working properly in your environment, what I will share with you may fix your issue. BTW I learned you can test the statconnDCOM API with a utility which comes installed in the statconn program folder. That utility should eliminate the API to R connectivity as an issue.

Here is the solution which worked in my case:

1) Delete the QlikView-R folder hierarchy you extracted from the demo zip file.

2) Redeploy the entire QlikView-R application from the original downloaded zip archive.

3) Open QlikView Desktop and go to Settings -> User Preferences.

4) Select the Export tab.

5) Under Default Export Options, choose ANSI encoding. I believe UTF-8 is the default.

6) Now try clicking the Run Risk Assessment button. If all goes as expected, the statconnDCOM banner should pop up and go away (without the macro editor window opening). The Risk of Mortality Decision Tree plot should also change and display the current date/time.

7) If that works, select a different set of Patient (IDs) and click the Run Risk Assessment button again. If your data changes and the plot updates, you’re in business.

Please post back your results.

-Brian

Re: QlikView and R Integration for Predictive Analytics Example

Brian_Bauer — Tue, 25 Jun 2013 14:43:08 GMT

N.,

Thank you for exploring the demo example. To be clear, the particular demo application in the package you downloaded is not designed to work from the QlikView Server. It is intended to be run through the QlikView Desktop. With regard to statconn, please refer to the statconn website for information on licensing of the API for commercial use. The URL is http://www.statconn.com/products.html.

Thank you.

-Brian

Re: QlikView and R Integration for Predictive Analytics Example

Mon, 08 Jul 2013 05:04:09 GMT

The macro is running fine but whatever the value i'm selecting from the patient id list box and running the 'macro run'

button, values are not reflecting on the text objects. What to do now?

My macro is :

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

LOGFILE_NAME = CurrentPath()&"/logs/R.log"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub runRScore

' Export the selected patient data to be scored.

Set myTable=ActiveDocument.GetSheetObject("DataSentToR")

myTable.Export CurrentPath&"/data/ExportFile.csv",",",0

' Create a COM object representing R

Set R = CreateObject("StatConnectorSrv.StatConnector")

R.Init "R"

R.EvaluateNoReturn "library(rattle)"

R.EvaluateNoReturn "building <- TRUE"

R.EvaluateNoReturn "scoring <- ! building"

' The colorspace package is used to generate the colours used in plots, if available.

R.EvaluateNoReturn "library(colorspace)"

' A pre-defined value is used to reset the random seed so that results are repeatable.

R.EvaluateNoReturn "crv$seed <- 42"

' Load the data.

R.EvaluateNoReturn "crs$dataset <- read.csv('"&CurrentPath&"/data/readmission.csv', na.strings=c('.', 'NA', '', '?'), strip.white=TRUE, encoding='UTF-8')"

' The following variable selections have been noted.

R.EvaluateNoReturn "crs$input <- c('enum', 't.start', 't.stop', 'time','event', 'chemo', 'sex', 'dukes','charlson')"

R.EvaluateNoReturn "crs$numeric <- c('enum', 't.start', 't.stop', 'time','event')"

R.EvaluateNoReturn "crs$categoric <- c('chemo', 'sex', 'dukes', 'charlson')"

R.EvaluateNoReturn "crs$target <- 'death'"

R.EvaluateNoReturn "crs$risk <- NULL"

R.EvaluateNoReturn "crs$ident <- c('record', 'id')"

R.EvaluateNoReturn "crs$ignore <- NULL"

R.EvaluateNoReturn "crs$weights <- NULL"

' Decision Tree

' The 'rpart' package provides the 'rpart' function.

R.EvaluateNoReturn "require(rpart, quietly=TRUE)"

' Reset the random number seed to obtain the same results each time.

R.EvaluateNoReturn "set.seed(crv$seed)"

' Build the Decision Tree model.

R.EvaluateNoReturn "crs$rpart <- rpart(death ~ ., data=crs$dataset[, c(crs$input, crs$target)], method='class', parms=list(split='information'), control=rpart.control(usesurrogate=0, maxsurrogate=0))"

' Score a dataset.

' Read a dataset from file for testing the model.

R.EvaluateNoReturn "crs$testset <- read.csv('"&CurrentPath&"/data/ExportFile.csv', na.strings=c('.', 'NA', '', '?'), header=TRUE, sep=',', encoding='UTF-8')"

' Obtain probability scores for the Decision Tree model on ExportFile.csv.

R.EvaluateNoReturn "crs$pr <- predict(crs$rpart, crs$testset[,c(crs$input)], type='class')"

' Extract the relevant variables from the dataset.

R.EvaluateNoReturn "sdata <- subset(crs$testset[,], select=c('record', 'id'))"

' Output the combined data.

R.EvaluateNoReturn "write.csv(cbind(sdata, crs$pr), file='"&CurrentPath&"/results/ExportFile_score_idents.csv', row.names=FALSE)"

' Create plot file for the Decision Tree.

R.EvaluateNoReturn "setwd('"&CurrentPath&"/New Plots')"

R.EvaluateNoReturn "png('R_Score_Plot_1_"&Runtime&".png')"

R.EvaluateNoReturn "plot(crs$rpart)"

R.EvaluateNoReturn "text(crs$rpart, use.n=TRUE)"

R.EvaluateNoReturn "title(main='Decision Tree', sub=format(Sys.time(), '%a %b %d %X %Y'), col.main='blue', col.sub='black', line='2', font.main=4, font.sub=3)"

R.EvaluateNoReturn "dev.off()"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Create Bar plot

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' The 'gplots' package provides the 'barplot2' function.

R.EvaluateNoReturn "require(gplots, quietly=TRUE)"

R.EvaluateNoReturn "set.seed(crv$seed)"

' Load the data.

R.EvaluateNoReturn "crs$dataset <- read.csv('"&CurrentPath&"/data/readmission.csv', na.strings=c('.', 'NA', '', '?'), strip.white=TRUE, encoding='UTF-8')"

' Generate the summary data for plotting.

R.EvaluateNoReturn "ds <- rbind(summary(na.omit(crs$dataset[crs$sample,]$sex)), summary(na.omit(crs$dataset[crs$sample,][crs$dataset[crs$sample,]$death=='0',]$sex)), summary(na.omit(crs$dataset[crs$sample,][crs$dataset[crs$sample,]$death=='1',]$sex)))"

' Sort the entries.

R.EvaluateNoReturn "ord <- order(ds[1,], decreasing=TRUE)"

R.EvaluateNoReturn "setwd('"&CurrentPath&"/New Plots')"

R.EvaluateNoReturn "png('R_Score_Plot_4_"&Runtime&".png')"

' Plot the data.

R.EvaluateNoReturn "bp <- barplot2(ds[,ord], beside=TRUE, ylab='Frequency', xlab='Sex', ylim=c(0, 455), col=rainbow_hcl(3))"

'Add the actual frequencies.

R.EvaluateNoReturn "text(bp, ds[,ord]+15, ds[,ord])"

'Add a legend to the plot.

R.EvaluateNoReturn "legend('topright', bty='n', c('All','0','1'), fill=rainbow_hcl(3))"

'Add a title to the plot.

R.EvaluateNoReturn "title(main='Distribution of sex (sample)by death', sub=paste(format(Sys.time(), '%a %b %d %X %Y')), col.main='blue', col.sub='black', line='2', font.main=4, font.sub=3)"

R.EvaluateNoReturn "dev.off()"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Close R connection

R.close

Set v = ActiveDocument.GetVariable("vRuntime")

v.SetContent Runtime(), True

' Load the scores into the QlikView application

ActiveDocument.DoReload 2, false,false 'Fail on error, Partial Reload

WriteLog("Retrieved Scores from R at "&Runtime&".")

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function CurrentPath()

Set v = ActiveDocument.GetVariable("vAppPath")

CurrentPath = Replace(v.GetContent.String,"\","/")

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function Runtime()

dtmThisDay = Right(Cstr(Day(Date) + 100),2)

dtmThisMonth = Right(Cstr(Month(Date) + 100),2)

dtmThisYear = Year(Date)

dtmThisHour = Right(Cstr(Hour(Time) + 100),2)

dtmThisMinute = Right(Cstr(Minute(Time) + 100),2)

dtmThisSecond = Right(Cstr(Second(Time) + 100),2)

Runtime = dtmThisYear & dtmThisMonth & dtmThisDay & dtmThisHour & dtmThisMinute & dtmThisSecond

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function openOutputFile(filename)

'Create and open an output file

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set openOutputFile = objFSO.OpenTextFile(filename, ForWriting, True)

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function WriteLog(message)

set logFile = openOutputFile(LOGFILE_NAME)

logFile.WriteLine(message)

logFile.Close

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Here I want to select some id's from the list box and trying to reflect the values as a tree model and as a bar plot on two different text object. The .png files are generating but the value is not reflecting there. I'm giving the Text objects code:

1. Here I'm showing the tree model

=vAppPath&'\New Plots\R_Score_Plot_1_'&$(vRuntime)&'.png'

2. Here I'm showing the Bar plot

=vAppPath&'\New Plots\R_Score_Plot_4_'&$(vRuntime)&'.png'

Thats all. Any suggestion will be helpful.

Re: QlikView and R Integration for Predictive Analytics Example

Sun, 14 Jul 2013 16:28:04 GMT

Apart from this , Is there any other sample code or step by step one page doc. avl. to implement this.. .

This attachment doc 2975 is not helpful unable to open the qvw file in the PE ???

Re: QlikView and R Integration for Predictive Analytics Example

Tue, 16 Jul 2013 11:12:34 GMT

@sathia--- its exactly true. I feel the same. Agree with you.

On Mon, Jul 15, 2013 at 8:05 PM, Samantha Gkonos

Re: QlikView and R Integration for Predictive Analytics Example

Thu, 18 Jul 2013 04:37:40 GMT

Its been long I'm facing the problem. But I have no idea how to solve the porblem. Anybody please help. Thanks in advance.

Re: QlikView and R Integration for Predictive Analytics Example

Thu, 08 Aug 2013 20:13:22 GMT

Hi Brian,

Having the same issue where the module opens up and yes the export encoding has been ANSI by default for me. I am guessing its something to do with the matching versions of rcom, rscproxy, statconnCOM as RandFriends is not available now. Pls could you put the older version file of RandFriends here or maybe mention the matching version nos.

Thanks,

Amit

Re: QlikView and R Integration for Predictive Analytics Example

danielgargiulo — Sun, 01 Sep 2013 21:16:21 GMT

Hi Brian,

I am unable to see the RAandFriends download at the link provided in the documentation: statconn

Do you know if this has been removed?

Thanks

Dan

Re: QlikView and R Integration for Predictive Analytics Example

danielgargiulo — Mon, 02 Sep 2013 06:57:10 GMT

Hi Brian,

As with Amit, I am also receiving the Macro Popping up after following the instructions you posted on 25th June above. Any ideas of how we can resolve or is there an earlier version we can install?

------------------------------------------------------------------------------------------------------------------------------

In response to my query above I have:

- download R from: The R Project for Statistical Computing

- statconnDcom non commercial from : statconn

Other Notes when installing the above:

- just before step 10 in the documentation, you will also need to type 'yes' to the prompt in R.You should then see the error as described in step 10.

- At the end rattle also prompted me to install serveral other files three time, i installed all but do not know if this was required or not.

Thanks

Dan