0 Replies Latest reply: Aug 10, 2018 12:42 AM by Algis Kulionis RSS

    Qlik Sense and R: returning of data frame

    Algis Kulionis

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

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