Qlikview and R example (Predictive Demo)

[#1]

Hi All,

I am pretty new to QlikView and have basic knowledge of R. I was trying the Predictive demo example and it worked out fine.

Now, I am trying to create a linear regression model in R and integrate the same with QlikView (LR model instead of a tree).

I would like to know how to display the results from R in QlikView. I am able to open the StatConnector, execute the commands in R, and store the results in a csv file. However, I do not understand how to get the results to display in QlikView.

Here is my macro -

Sub runRScore

     ' Export the selected patient data to be scored.

     Set myTable=ActiveDocument.GetSheetObject("DataSentToR")

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

     ' Create a COM object representing R

  Set R = CreateObject("StatConnectorSrv.StatConnector")

  R.Init "R"

     R.EvaluateNoReturn "data <- read.csv('C:/Users/user/Desktop/Data.csv', na.strings=c('.', 'NA', '', '?'), strip.white=TRUE, encoding='UTF-8')"    

     R.EvaluateNoReturn "model <- lm(TOT_AMT ~YEARWK + AVGRATE + CMFLAG + SEASON + ROW_NUM, data = data)"

     R.EvaluateNoReturn "test <- read.csv('C:/Users/user/Desktop/ExportFile.csv') "

     R.EvaluateNoReturn "test$TOT_AMT <- NULL"

     R.EvaluateNoReturn "results <- predict(model, test)"

     R.EvaluateNoReturn "write.csv(cbind(test,results), file = 'C:/Users/user/Desktop/Results.csv') "

         ' 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

Any help would be greatly appreciated!!!

Thanks,

Durga.

[Anonymous]

Hi,

you have to add a load statement to your script (not to macro) which is executed during the partial reload which is triggered by your macro:

add load*

from

(txt, utf8, no labels, delimiter is ';', msq);

depending on your file format of the R export you have to change the format options in your load.

Hope this helps.

Best regars

Stefan

[martinpohl]

Hello,

in QlikView the work with data is diffenrent to other data warehouses.

You can load all of your data into memory.

By selecting values (customer name, martial no month) QlikView is filtering the results in tables and charts by himself.

There is no reloading from datas needed.

Regards

[#4]

Hi Stefan,

Thanks a ton. However, I'm still encountering an issue. I have two tables - one showing the original data, and the other showing the predicted results (TB03 is the object ID). When I do a partial reload, the table still shows the previous values and not the predicted ones. Can you point out where I am going wrong? Or if you could point me to a useful resource, that'd be great too!

Thanks so much for your time. 

LOAD YEARWK,

     TOT_AMT,

     TOT_QTY,

     AVGRATE,

     CMFLAG,

     SEASON,

     ROW_NUM

FROM

(ooxml, embedded labels, table is [BigEats-452]);

TB03:LOAD ROW_NUM,

     results as TOT_AMT

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Durga.

[Anonymous · Accepted solution]

Hi,

add an 'add' statement to your load script. You also need to create another object which shows the results (add ROW_NUM and TOT_AMT to this object) and trigger the macro again:

LOAD YEARWK,

     TOT_AMT,

     TOT_QTY,

     AVGRATE,

     CMFLAG,

     SEASON,

     ROW_NUM

FROM

(ooxml, embedded labels, table is [BigEats-452]);

TB03:

add

LOAD ROW_NUM,

     results as TOT_AMT

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Hope this helps

Best regards

Stefab

[#6]

Hi Stefan,

Thanks a ton for your help. I finally figured it out.

I used the add script and all of the tables would get reloaded with the results. I just wanted to load results on a single table box in the sheet. Then I figured that in the demo example, the results were stored under different column names (as _id, _amt, etc).

Now it works perfectly fine, it was a silly oversight on my part.

Thanks!

[Anonymous]

Dear Durga

Are you able to share your regression QV document?

Regards.

Chris