R shiny dashboard Plot interactive data subquery error

Question

I am getting error message while trying to add a subquery of dataset before generating the plot in Shiny. I used sqldf to subset the dataset from a dynamic input on slider input in UI. Error message was listed below. Not sure what happen.

'Warning: Error in : near "[[input$namesn_chg]": syntax error [No stack trace available]'

Layout in app:

UI.R:

tabPanel(title = "Spaghetti",
                                actionButton("spaghetti_update", "Update Filtered Dataset"),

                                selectInput("namesn_chg", "Select PCHG Variable", choices = "Import Dataset to Begin"),
                                sliderInput("spaghetti_slider", "More than % of Increase/Decrease:", -100, 100, 60),
                                actionButton("spaghetti_render", "Render Plot")

Server.R:

  filtered_dataset_spaghetti <- reactiveVal()
  
  observeEvent(input$spaghetti_update,{
    if(is.null(filter_tbl_cols()$Variable)){return(NULL)}
    dat <- dataset()
    filter_cols <- filter_tbl_cols()$Variable
    filtered_dataset_spaghetti(dat[filter_rows(), ..filter_cols])
    output$spaghetti_uploaded <- renderText({paste0("UPDATED")})
    output$spaghetti_uploaded_vars <- renderText({paste0("Filtered Dataset contains ", filtered_dataset_spaghetti()[,.N], " Observations and ", length(names(filtered_dataset_spaghetti())), " Variables")})
  })
  
  observeEvent(input$spaghetti_update, {
    if(is.null(dataset())){return(NULL)}
    filtered_varlist_all <- names(filtered_dataset_spaghetti())
    updateSelectInput(session, "spaghetti_x", choices = sort(filtered_varlist_all))
    updateSelectInput(session, "spaghetti_y", choices = sort(filtered_varlist_all))
    updateSelectInput(session, "spaghetti_by", label = "Select Group By Variable", choices = c("NA", sort(filtered_varlist_all)))
    updateSelectInput(session, "spaghetti_sort", label = "Sort x-axis Variable By:", choices = sort(filtered_varlist_all), selected = input$spaghetti_x)
    updateSelectInput(session, "namesn_chg", label = "Select PCHG Variable", choices = sort(c(varlistn(), varlistdt())))
  })

  observeEvent(input$spaghetti_render,{
    if(is.null(dataset()) | is.null(input$spaghetti_sort)){return(NULL)}
    if(input$spaghetti_by == "NA"){
      spaghetti_by <- "NA"
    }else{#Transforming x-axis variable into factor, grouped by a sort key. This tells ggplot2 to 'order by' the factor variable. The below process is necessary since we cannot assign '<-' to a function, in this case filtered_dataset_box().
      sort_dat <- filtered_dataset_spaghetti()
      #Add slide input for chg_pre
      sort_dat <- sqldf("select *
            from filtered_dataset_spaghetti()
            where SUBJID in (
            select SUBJID   
            from filtered_dataset_spaghetti() where filtered_dataset_spaghetti()[[input$namesn_chg]]>input$spaghetti_slider
            )"
      )
      
      sort_key <- input$spaghetti_sort
      sort_dat[[input$spaghetti_x]] <- factor(sort_dat[[input$spaghetti_x]], levels = unique(sort_dat[order(sort_dat[, ..sort_key])][[input$spaghetti_x]]))
      filtered_dataset_spaghetti(sort_dat)
      
      spaghetti_by <- filtered_dataset_spaghetti()[[input$spaghetti_by]]
    }

one bracket is missing in [[input$namesn_chg]? [[input$namesn_chg]] intead. — jpdugo17
@jpdugo17, as you see in the code, it is '[[input$namesn_chg]]', there is only one '[[input$namesn_chg]]' in the code. — qifengwu
In the sql statement from filtered_dataset_spaghetti() is invalid syntax. from must be followed by the name of a data frame, not a function call. — G. Grothendieck

Ronak Shah Ronak Shah · Accepted Answer · 2021-06-07T03:06:27

Instead of sqldf can you try this dplyr code ?

sort_dat <- sort_dat %>%
  filter(SUBJID %in% SUBJID[.data[[input$namesn_chg]] > input$spaghetti_slider])

R shiny dashboard Plot interactive data subquery error

1 Answers