Example of Qlikview and R data integration

[#1]

I recently finished a test of data integration of Qlikview and R. I used data from Indian Water Quality project at Kaggle to develop a prototype for plotting a heatmap upon setting a filter in Qlikview dashboard. Thanks to Brian Bauer (bbu) for his post QlikView and R Integration for Predictive Analytics Example the which I used as reference for this development.

Upon clicking on the button "Generate Heat Map" a VB script is executed to initiate a session in R and plot a heatmap using ggplot library.

The procedure followed to generate such a graph was

1. 1.- Export data from Qlikview to a CSV text file
2. 2.- Declare and initialise a R session using library StatconnDcom
3. 3.- Prepare data using a R function sourced from Github
4. 4.- Plot the heatmap using function draw_hm, that call in turn ggplot function, into a png file

Below you can see the VB script used for this purpose. Enjoy!

Sub plotHeatmap

          ' Export the selected patient data to be scored.

          Set myTable = ActiveDocument.GetSheetObject("DataSentToR")

          myTable.Export (CurrentPath & "/data/ExportFile.csv"), ";"

          ' Create a COM object representing R

          Set R = CreateObject("StatConnectorSrv.StatConnector")

    R.Init "R"

    R.EvaluateNoReturn "library(rattle)"

    R.EvaluateNoReturn "library(devtools)"

    R.EvaluateNoReturn "library(ggplot2)"

    R.EvaluateNoReturn "setwd(" & "'indian-water-quality'" & ")"

    CurrentPathWindows = Replace(CurrentPath, "/", "\\")

    R.EvaluateNoReturn "crs$water.quality1<-read.csv(file=" & "'" & CurrentPathWindows & "\\data\\ExportFile.csv" & "'" & ",sep=';'" & ")"

    R.EvaluateNoReturn "saveRDS(crs$water.quality1, file = 'water.quality1.rds')"

    R.EvaluateNoReturn "names(crs$water.quality1)<-c( 'StateName','DistrictName','BlockName','PanchayatName','VillageName','HabitationName','Year','QualityParameter')"

' source("water-quality-data-preparation-v3.R")

    R.EvaluateNoReturn "source('https://github.com/rventuradiaz/indian-water-quality/raw/master/water-quality-data-preparation-v4.R')"

    R.EvaluateNoReturn "wq_df <-wq1_df(crs$water.quality1)"

    R.EvaluateNoReturn "png('Rplot_Panchayat_ggplot.png')"

    R.EvaluateNoReturn "plothm <- draw_hm(data=wq_df, x=wq_df$Year, y=wq_df$PanchayatName, fillInput = wq_df$QualityParameter, facetInput = . ~ BlockName, fill_discrete='QualityParameter')"

    R.EvaluateNoReturn "print(plothm)"

    R.EvaluateNoReturn "dev.off()"

          ' Close R connection

        R.close

End Sub

[#2]

Hi Rafael,

thank you for your efforts in providing this discussion.

I'm trying to get oriented in QlikView and in particular, how to integrate it with R.

One query on your discussion above in relation to the first step in the process you've outlined:

1.1. Export data from Qlikview to a CSV text file.

where did this data originally come from?  i.e. how did it get imported into QlikView in the first place?

cheers

Matt

[#3]

Hi Matt,

Data originally came from Kaggle. You can see a reference in my blog (Indian Water Quality Visualization Using Heatmaps | Data Science for Water Project's Business Analys...) or going to original source in Kaggle (https://www.kaggle.com/venkatramakrishnan/india-water-quality-data)

Data was downloaded in CSV textfile format. I uploaded into Qlikview and developed the Qlikview dashboard from it.

Best regards,

Rafael