24 Replies Latest reply: Mar 20, 2015 1:28 PM by Enmanuel Santana RSS

    QlikView and R

    Joao Frasco

      Hi

       

      Has anyone attempted getting QlikView and R talking to eachother?

       

      I would like to do advanced statistical computing in R without having to go via Excel.

       

      Thanks.

       

      Joao.

          • Re: QlikView and R
            Joao Frasco

            Thank you very much for this.

             

            I will try it out.

             

            Joao.

            • Re: QlikView and R
              Joao Frasco

              Hi Henric

               

              Thank you very much for this. i have got it working. Unfortunately, I'm still fairly new to QlikView and want to learn what I can bring back into QlikView from R, slowly, by working through the lines of code in the example. I'm also concerned that certain things may not be possible to bring back.

               

              I would therefore like to ask if there is anyway to see the information in R once it has been passed through. I have R open, and can pass the information through, but I can't see it in the R session. I think this is becasue the code initializes a new R session, but don't know if this is the reason.

               

              Is there any way to pass the information through to R, and then go an play with it in R?

               

              Thanks.

               

              Joao.

                • Re: QlikView and R
                  Elif Tutuk

                  Hi Joao-

                   

                  The example that is available in the kit (Henric provided the link above) uses a R COM object to allow QlikView and R communication. This communication is 2 ways; QV can pass data to R, and QV can get data from R (Please note that the method that is used to get the data from R to QV -R.GetSymbol- may not be supported with the latest version of R, I think there is a new method but I did not have the chance to research). But the solution already does what you are asking. If you want to pass the data from QV to R, and then continue to interact with it in R, you can use the "copytoclipboard" option in QV to copy the selected data into clipboard and then you can use the R functionality to get the data from clipboard to R and continue to interact with the data in R. I hope this makes sense.

                   

                  Thanks a lot,

                   

                  Elif.

                    • Re: QlikView and R
                      Joao Frasco

                      Thanks Elif.

                       

                      I have resolved the problem by using Rcom instead of RDcom, which allows me to see the data passed through to R in my existing R session. I'll now work on understanding exactly what can be passed back into QV (data only, or other things like plots).

                       

                      Thank you very much for the assistance.

                       

                      Joao

                       

                      Sent from my iPad

                        • Re: QlikView and R
                          Joao Frasco

                          I'm now getting an error 'Type mismatch' when trying to update my values in QlikView. Unfortunately, I can't debug the code to see what is being pulled through and where it is failing. If anyone has any ideas, your input would be welcome.

                           

                          Thanks.

                           

                          Joao

                            • Re: QlikView and R

                              I'm getting the same error!

                              I can get the data from R to Qlikview, with the correct count (check it by UBound), but I can't show any data, it will say "Type mismatch".

                                • Re: QlikView and R
                                  Joao Frasco

                                  I got this to work by passing through one value at a time. Seeing as I was looping anyway to write the values into my data, I ask R for one value at a time.

                                   

                                  Sent from my iPad

                                    • Re: QlikView and R

                                      Thank you. It really works!

                                      One data a time, very uncomfortable.

                                        • Re: QlikView and R
                                          Joao Frasco

                                          You're welcome.

                                           

                                          I also change the connector to Rcom, and then I can see all my data in R directly, which makes it a lot easier to debug any problems. Try this out if you're having problems. Essentially, you're using the foreground server instead of the background server.

                                           

                                          Sent from my iPad

                                            • Re: QlikView and R

                                              Hi jcfrasco,

                                                   How can I change the connector to Rcom?

                                               

                                              Martin

                                                • Re: QlikView and R
                                                  Joao Frasco

                                                  Will need to look when I'm back at work tomorrow. Will let you know then.

                                                   

                                                  Sent from my iPad

                                                  • Re: QlikView and R
                                                    Joao Frasco

                                                    Use this instead:

                                                     

                                                    Set R = CreateObject("RCOMServerLib.StatConnector")

                                                     

                                                    Joao

                                                     

                                                     

                                                    STANLIB disclaimer and confidentiality notice:

                                                    This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of STANLIB. Any views or opinions presented are solely those of the author and do not necessarily represent those of STANLIB. This e-mail is subject to terms available at the following link: http://www.stanlib.com/Pages/Disclaimer.aspx.

                                                     

                                                    STANLIB is an authorised financial services provider.

                                                      • Re: QlikView and R
                                                        Ivan Mak

                                                        I've changed mine, but get this error "ActiveX component can't create object: 'RCOMServerLib.StatConnector' "

                                                         

                                                        Should I download a new package from somewhere?

                                                          • Re: QlikView and R
                                                            Joao Frasco

                                                            Hi Ivan

                                                             

                                                            I don’t know enough about this to know whether it is a package issue, but I would try to re-download the packages. A few quick questions:

                                                             

                                                             

                                                            1.       Does your full statement look like this: Set R = CreateObject("RCOMServerLib.StatConnector")

                                                             

                                                            2.       Did it work before you made the change?

                                                             

                                                            3.       Do you have the following packages: rcom and rcsproxy

                                                             

                                                            Regards,

                                                             

                                                            Joao

                                                             

                                                             

                                                            STANLIB disclaimer and confidentiality notice:

                                                            This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of STANLIB. Any views or opinions presented are solely those of the author and do not necessarily represent those of STANLIB. This e-mail is subject to terms available at the following link: http://www.stanlib.com/Pages/Disclaimer.aspx.

                                                             

                                                            STANLIB is an authorised financial services provider.

                                                    • Re: QlikView and R
                                                      Andrea Gigliotti

                                                      Hi Martin,

                                                       

                                                      Could you please send me the VBScript code used (looping) to fix the .qvw example ?

                                                       

                                                      Many Thanks

                                                       

                                                      Regards

                                                      Andrea

                                                        • Re: QlikView and R

                                                          Hi Andrea,

                                                               I first read the total number of the data and then read the result data one by one and it will work. The coding is ugly...

                                                           


                                                          UU=UBound(RResult)

                                                          msgbox("I received from R : " & UU+1)

                                                          for i=0 to UU


                                                          R.EvaluateNoReturn "outdata<-fit$cluster[" & i+1 & "]"


                                                          RResult=R.GetSymbol("outdata")


                                                          set result=ActiveDocument.DynamicUpdateCommand ("UPDATE * SET new_group=" & RResult & " WHERE ID=" & mySelections(i))

                                                          next
                                                            • Re: QlikView and R
                                                              Andrea Gigliotti

                                                              Hi Martin,

                                                               

                                                              Many thanks for your help.

                                                              I solved using the below code:

                                                              ---

                                                              For i = 0 To UBound(mySelections)

                                                                        vCluster = R.Evaluate("QVData[" & (i + 1) & ",4]")

                                                                          set result = ActiveDocument.DynamicUpdateCommand ("UPDATE * SET FitCluster=" & vCluster & "WHERE [Zip Code]='" & mySelections(i) &"'")

                                                              Next

                                                              ---

                                                              However your post is very useful.

                                                               

                                                              Regards

                                                              Andrea

                                                • Re: QlikView and R

                                                  Hi Elif,

                                                       If the GetSymbol is not supported in the latest R, which verion of R is it supporting?

                                                       I wonder if the author of the demo can run the demo or not.

                                                   

                                                  Martin

                                                  • Re: QlikView and R

                                                    Hola Elif Tutuk:  Estoy haciendo predicciones con R pero necesito poder visualizar la información en QlikView, estoy usando el conector statcon Dcom, pero no sé qué configuar en la macro, mis resultados están efectuados en R sólo falta mostrarlo en QlikView.

                                                    Quedo atenta a tus comentarios, gracias


                                              • Re: QlikView and R

                                                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"

                                                 

                                                          ' Rattle is Copyright (c) 2006-2012 Togaware Pty Ltd.

                                                          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
                                                  Enmanuel Santana

                                                  this is another technique without macro (en español):

                                                  http://qlikviewapuntes.blogspot.com/2015/03/qlikview-r-para-kmeans.HTML

                                                   

                                                  qlikview_mas_R.jpg