I am trying to create a dashboard by pulling data from different database tables from the conditions in a csv file.
Spec_CSV <- read.csv("/home/..../data/Spec Sheet.csv",header=T, sep=",",stringsAsFactors = TRUE)
Series Group AttributeName GroupType MOC Condition
T59 DETAILS Attribute 55 Primary M
T80 FLAGS Attribute 76 Repeating C If Attribute 77 is populated then this values has to be populated
T81 FLAGS Attribute 77 Repeating O
T82 FLAGS Attribute 78 Repeating M
T30 DETAILS Attribute 26 Primary M
T31 DETAILS Attribute 27 Primary C If Attribute 26="ABC" then values has to be populated
T32 DETAILS Attribute 28 Primary M
T33 DETAILS Attribute 29 Primary O
I am pulling the conditions individually which makes the code very tedious and the page slow.
#Logic for Details for Mandatory Fields
detailsMandatory<-reactive({
columnnamesfordetailsman<-Spec_CSV$AttributeName[Spec_CSV$MOC == "M" & Spec_CSV$Group=="DETAILS"]
columnnamesfordetailsman_comma=noquote(paste("a.",as.character(columnnamesfordetailsman),sep="",collapse=","))
#print(columnnamesfordetailsman_comma)
})
readDetailsforMandatory <- eventReactive(input$pick_dt,{isolate(
if(input$pick_assetclass!=c(NULL,'NA') && input$pick_assetclass=="FX" && input$pick_feed!=c(NULL,'NA') && input$pick_src!=c(NULL,'NA') && input$pick_dt!=c(NULL,'NA')){
sqlInput1 <- paste("select ",detailsMandatory(),"
from DETAILS a left join ",outputMart()," b on a.trade_id=b.trade_id where b.FEED=","'",input$pick_feed,"'",
"and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'"," order by a.trade_id",
sep="")
dbGetQuery(con, sqlInput1)}
)})
detailsmand<-reactive({
missper <- signif(sum(is.na(readDetailsforMandatory()))/prod(dim(readDetailsforMandatory()))*100,digits=4)
misscount <- sum(is.na(readDetailsforMandatory()))
data.frame("Trade Attributes","M",missper,misscount)})
#Logic for Flags for Mandatory Fields
flagsMandatory<-reactive({
columnnamesforflagsman<-Spec_CSV$AttributeName[Spec_CSV$MOC == "M" & Spec_CSV$Group=="FLAGS"]
columnnamesforflagsman_comma=noquote(paste("a.",as.character(columnnamesforflagsman),sep="",collapse=","))
#print(columnnamesforflagsman_comma)
})
readFlagsforMandatory <- eventReactive(input$pick_dt,{isolate(
if(input$pick_assetclass!=c(NULL,'NA') && input$pick_assetclass=="FX" && input$pick_feed!=c(NULL,'NA') && input$pick_src!=c(NULL,'NA') && input$pick_dt!=c(NULL,'NA')){
sqlInput1 <- paste("select ",flagsMandatory(),"
from FLAGS a left join ",outputMart()," b on a.trade_id=b.trade_id where b.FEED=","'",input$pick_feed,"'",
"and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'"," order by a.trade_id",
sep="")
dbGetQuery(con, sqlInput1)}
)})
flagsmand<-reactive({
missper <- signif(sum(is.na(readFlagsforMandatory()))/prod(dim(readFlagsforMandatory()))*100,digits=4)
misscount <- sum(is.na(readFlagsforMandatory()))
data.frame("Flag Attributes","M",missper,misscount)})
#Logic for Flags for Optional Fields
flagsOptional<-reactive({
columnnamesforflagsopt<-Spec_CSV$AttributeName[Spec_CSV$MOC == "O" & Spec_CSV$Group=="FLAGS"]
columnnamesforflagsopt_comma=noquote(paste("a.",as.character(columnnamesforflagsopt),sep="",collapse=","))
#print(columnnamesforflagsopt_comma)
})
readFlagsforOptional <- eventReactive(input$pick_dt,{isolate(
if(input$pick_assetclass!=c(NULL,'NA') && input$pick_assetclass=="FX" && input$pick_feed!=c(NULL,'NA') && input$pick_src!=c(NULL,'NA') && input$pick_dt!=c(NULL,'NA')){
sqlInput1 <- paste("select ",flagsOptional(),"
from FLAGS a left join ",outputMart()," b on a.trade_id=b.trade_id where b.FEED=","'",input$pick_feed,"'",
"and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'"," order by a.trade_id",
sep="")
dbGetQuery(con, sqlInput1)}
)})
flagsoptional<-reactive({
missper <- signif(sum(is.na(readFlagsforOptional()))/prod(dim(readFlagsforOptional()))*100,digits=4)
misscount <- sum(is.na(readFlagsforOptional()))
data.frame("Flag Attributes","O",missper,misscount)})
#Consolidated Data Table
consolidatedresults<-eventReactive(input$pick_cobdt,{isolate(
rbindlist(list(detailsmand(),flagsmand(),flagsoptional()),idcol=TRUE)
)})
output$consolidated<-renderDataTable((datatable(data.frame(consolidatedresults())
,class = 'cell-border stripe',
colnames = c('Table Name', 'M/O/C','Missing %','Missing Count'),
rownames = FALSE,options = list(
pageLength = 25,autoWidth = TRUE,dom = 't')))
)
What is the best possible way to loop through the csv and pass the results to the database and get the results at one go with minimal lines of code? I have tried to use split function on the csv but stuck with how to pass the results to the database query at one go.
data.dplyr <-split(Spec_CSV$AttributeName,list(Spec_CSV$Group,Spec_CSV$MOC))
data.dplyr.result<-sapply(data.dplyr,function(x)paste("a.",as.character(x),sep="",collapse=","))
Thanks,