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);
------------------------------------------------------
