Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

a_kulionis
New Contributor

Qlik Sense and R: returning of data frame

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

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

Community Browser