Qlik Sense and R: returning of data frame

a_kulionis — Thu, 09 Aug 2018 18:30:04 GMT

Hello everyone,

Please, could you help me with Qlik Sense and R integration.

I am solving Market Basket Analysis task I don't know how to return calculated results (data frame from a few columns) directly to Qlik sense from R script?

My steps:

1. In Qlik Sense I have transaction table Transactions (Tran_No; Product).

2. During load I pass this table (Transactions) to R.

3. R makes calculations (in this case market basket analysis with apriori algorithm) and the result is rules: dataframe from 5 columns (rules; support; confidence; lift; count). These rules are saved to file df_rules.csv.

4. During the same load I upload df_rules.csv as table to Qlik Sense and make visualization.

What I whant is to get the result of calculations - df_rules (step 3) directly to Qlik sense without saving to csv file or making additional transformations.

My script:

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

SET vRscript='library(arules);library(arulesViz); library(datasets); library(reshape); df_sales<-data.frame(q$Tran_No,q$Product); colnames(df_sales)<-c("Tran","Prod"); trans1 <- as(split(df_sales[,"Prod"], df_sales[,"Tran"]), "transactions"); rules <- apriori(trans1, parameter = list(supp = 0.01, conf = 0.8)); redundant_rules<-is.redundant(rules); rules<-rules[!redundant_rules]; rules<-sort(rules, by="support", decreasing=TRUE); df_rules <- as((rules), "data.frame"); df_rules$rules<-as.character(df_rules$rules); write.csv(df_rules, "C:/Users/Documents/df_rules.csv",quote = TRUE, row.names = TRUE); paste(df_rules$rules,df_rules$support,df_rules$confidence,df_rules$lift,sep = "_");';

#Actually "paste(df_rules$rules,df_rules$support,df_rules$confidence,df_rules$lift,sep = "_")" is not needed. It is possible to return even string like this "return text"

#------------------load of transactions (1 step)

Transactions:

LOAD

Tran_No,

Product

FROM [lib://R_testing (user1)/Sale.qvd]

(qvd);

#------------------R (2-3steps)

MBA_table:

LOAD

EXTENSION R.ScriptEvalExStr('$(vRscript)', Transactions{Tran_No, Product});

// ---------------- Load R results (4 step)

rules_table:

LOAD

rules,

support,

confidence,

lift,

"count"

FROM [lib://R_testing (user1)/df_rules.csv]

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

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

topic Qlik Sense and R: returning of data frame in Integration, Extension & APIs

Qlik Sense and R: returning of data frame