how to use multiple key with SP

siddharth_sharma · November 7, 2019, 5:18am

(go all the way down to be specific, check out the last post to avoid any kind of confusion)

I am using a stored procedure on ms SQL server and storing the parameter as dynamic in the keyword named key used in shiny and i want to split these two values one before tilde and one after it, i wanna separate or split them into two distinct or separate value and store them two different variables, how to get it done

the screenshot given below:

key = ~paste(dtd$StateCode,sep="~",dtd$DID))

and the stored procedure looks like this...

qrydd=paste("exec database_name..[sp_name]'','','','','','2','','','01/01/2017','31/12/2017'")

The code is given below...

library(shiny)
ui<fluidpage(
fluidrow(plotlyoutput("plott6")),
fluidrow(plotlyoutput("plott7"))
)

server<-function(input,output){

output$plott6 <- renderPlotly({
 d<-event_data('plotly_click',source = 'linkhere')
 if(is.null(d)==F){
qrydd=paste("exec db_name..[sp_name]",d$key,",'','','','','0','2','15','','01/01/2017','31/12/2017'")
    qrydd
    dtd <- sqlQuery(dbcnd,qrydd)
    dtd <- data.frame(dtd)
    dtd
    closeAllConnections()
    layout <- list(
      font = list(size = 12),
      title = "",
      xaxis = list(title = "district"),
      yaxis = list(title = "records",automargin = TRUE)
    )
    p <- plot_ly(dtd, colors = COL, marker = list(color = COL),source = "link10",key = ~paste(dtd$StateCode,sep="~",dtd$DID)) %>%
     add_trace(x = dtd$DistrictName, y = dtd$ToOtherState,name = "",type = 'bar') %>%
     layout(font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis) 
     }
 else
 {  return(NULL)}
})

output$plott7 <- renderPlotly({
 d<-event_data('plotly_click',source = 'link10')
 if(is.null(d)==F){

qrydd=paste("exec db..[sp]'2','101','','','','4','2','','','01/01/2010','31/12/2017' ")
    qrydd
    dtd <- sqlQuery(dbcnd,qrydd)
    dtd <- data.frame(dtd)
    dtd
    closeAllConnections()
    layout <- list(
      font = list(size = 12),
      title = "",
      xaxis = list(title = "records"),
      yaxis = list(title = "center_name",automargin = TRUE)
    )

    p <- plot_ly(dtd, colors = COL, marker = list(color = COL),orientation='h',source = "linkedhere2") %>%
     add_trace(x =dtd$TotOtherState , y = dtd$CenterName,name = "",type = 'bar') %>%
     layout(font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)}
 else{ return(NULL)} })
}
shinyApp(ui,server)

no need of code explantion ,just how to break down the key value into two parts and store them two variable,even an idea would serve my purpose,any suggestion would be great.

mishabalyasin · November 7, 2019, 2:47pm

I've asked you on one of your previous questions, but I will ask again - why did you tag this question with shiny tag? It makes it difficult to search for relevant questions. I'm pretty sure this particular question is not relevant to shiny in any way, it's just a context in which you are using it. It's a general problem, so tagging it properly would highlight what your question is about and increase your chances to get a response.

Second thing I've already asked and which will greatly increase chances for response is reprex. Your question is fairly straightforward, I think, but personally I won't spend my time copying data from your screenshot just to help you. Moreover, the whole point of reprex is that while preparing one, you'll actually flesh out what is your problem and it will be much easier for someone to help you.

siddharth_sharma · November 7, 2019, 3:47pm

It's becoz I am not looking for complete code for this what I really need is just an idea or just a single line of snippet would serve my purpose, in my other questions I have given reprex where I am looking for complete code solution and you are right about tag, I will take care next time, thanks for your concern though and i have checked the drop down options of tag,there are limited categories,not specified what to choose from,not adaptable to newbie like me, what to do if i wanna specify any particular event or some thing like that,how to approach that,all i need is just little bit guidance,not being rude or anything,i welcome any sort of suggestion ,i hope you got what i am tryna explain here

siddharth_sharma · November 19, 2019, 9:52am

    library(shinyjs)
    library(plotly)
    library(shinydashboard)
    library(shinycssloaders)
    library(shiny)
    library(dplyr)
    library(DT)
    library(tidyr)
    library(shinycustomloader) 
    library(tibble)
    library(datapasta)
    
    COL = c("#293a80","#39375b","#6915cf","#4b8e8d","#d55252","#293462","#940a37","#f54291","#f0134d","#b22222","#3c4245","#5d1451","#3c3d47")
    
    
    ui<- fluidPage(
      fluidRow(column(width=12,
                      withLoader(plotlyOutput("plott1"),type = "html",loader = "loader4"))),
      fluidRow(column(width=12,
                      withLoader(plotlyOutput("plott2"),type = "html",loader = "loader1"))),
      fluidRow(column(width=12,
                      withLoader(plotlyOutput("plott3")))),
      fluidRow(column(width=12,
                      withLoader(plotlyOutput("plott4")))),
      fluidRow(column(width=12,
                      withLoader(plotlyOutput("plott5")))),
      fluidRow(verbatimTextOutput("plott6"))
      )
    
    server<- function(input,output)
      
    {
      output$plott1 <- renderPlotly({
        sp1<-tibble::tribble(
          ~DID, ~DistrictName, ~BlockName, ~CenterName, ~TotRaj, ~TotOtherState, ~StateCode,
          76,       "state",     "null",      "null", 7656454,        5645345,     "null"
        )
        
        
        ds <- data.frame(labels = c("Rajasthan","Other_State"),
                         values = c(sp1$TotRaj,sp1$TotOtherState)
        )
        
        plot_ly(ds, labels = ~labels, values = ~values,type = 'pie',source ='listenhere1',
                textposition = 'inside',
                textinfo = 'label+percent',
                insidetextfont = list(color = '#FFFFFF'),
                marker = list(colors = c('#5d1451','#745c97'),
                              line = list(color =colors , width = 1)),showlegend = FALSE) %>% 
          layout(
            xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
            yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
      }) 
      
      output$plott2 <- renderPlotly({
        s <- event_data("plotly_click", source = "listenhere1")
        req(!is.null(s))
        
        if(s$pointNumber == 0){
          
          sp2<-tibble::tribble(
            ~DID, ~DistrictName, ~BlockName, ~CenterName, ~TotRaj, ~TotOtherState, ~StateCode,
            101,           "a",     "null",      "null",  564534,         564534,     "null",
            201,           "b",     "null",      "null",  675645,         765645,     "null",
            301,           "c",     "null",      "null",  765645,         786756,     "null",
            401,           "d",     "null",      "null",  987656,         764534,     "null",
            501,           "e",     "null",      "null",  675645,         543423,     "null",
            601,           "f",     "null",      "null",  765434,         231234,     "null",
            701,           "g",     "null",      "null",  564534,         763423,     "null",
            801,           "h",     "null",      "null",  234565,         567876,     "null",
            901,           "i",     "null",      "null",  985646,         876345,     "null",
            102,           "j",     "null",      "null",  876754,         453675,     "null",
            202,           "k",     "null",      "null",  876756,         876754,     "null"
          )
          
          layout <- list(
            font = list(size = 12),
            title = " District-Wise",
            xaxis = list(title = "district"),
            yaxis = list(title = "records",automargin = TRUE)
          )
          
          p <- plot_ly(sp2, x = sp2$DistrictName, y = sp2$TotRaj, type = 'bar', name = '',source = 'link3',key = ~paste(sp2$DID, sep = ""),marker = list(color = '#baabda')) %>%
            add_trace(y = sp2$TotOtherState, name = 'Other_State',marker = list(color = '#58508d')) %>%
            layout(yaxis = list(title = ''), barmode = 'stack')
        }
        else {
          sp2<-tibble::tribble(
            ~DID, ~DistrictName, ~BlockName, ~CenterName, ~TotRaj, ~TotOtherState, ~StateCode,
            101,           "a",     "null",      "null",  564534,         564534,     "null",
            201,           "b",     "null",      "null",  675645,         765645,     "null",
            301,           "c",     "null",      "null",  765645,         786756,     "null",
            401,           "d",     "null",      "null",  987656,         764534,     "null",
            501,           "e",     "null",      "null",  675645,         543423,     "null",
            601,           "f",     "null",      "null",  765434,         231234,     "null",
            701,           "g",     "null",      "null",  564534,         763423,     "null",
            801,           "h",     "null",      "null",  234565,         567876,     "null",
            901,           "i",     "null",      "null",  985646,         876345,     "null",
            102,           "j",     "null",      "null",  876754,         453675,     "null",
            202,           "k",     "null",      "null",  876756,         876754,     "null"
          )
          
          tablename=rowSums(cbind(sp2$TotRaj,sp2$TotOtherState),na.rm=TRUE)
          
          layout <- list(
            font = list(size = 12),
            title = " Other-State",
            xaxis = list(title = "RECORDS"),
            yaxis = list(title ="STATES" ,automargin = TRUE)
          )
          
          p <- plot_ly(sp2, colors = COL, marker = list(color = COL),source = 'linkhere',orientation='h',key = ~paste(sp2$DID, sep = "")) %>%
            add_trace( x =sp2$TotOtherState,y = sp2$StateName,name = "states",type = 'bar') %>%
            layout( font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)
        }
      })
      
      output$plott3 <- renderPlotly({
        
        d<-event_data('plotly_click',source = 'link3')
        if(is.null(d)==F){
          sp3<-data.frame(stringsAsFactors=FALSE,
                          DID = c(101, 101, 101, 101, 101, 101, 101, 101, 101, 101, 101),
                          DistrictName = c("a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a"),
                          BlockName = c("aaa", "bbb", "ccc", "ddd", "eee", "fff", "ggg", "hhh",
                                        "iii", "jjj", "kkk"),
                          CenterName = c("null", "null", "null", "null", "null", "null", "null",
                                         "null", "null", "null", "null"),
                          TotRaj = c(564534, 675645, 765645, 987656, 675645, 765434, 564534,
                                     234565, 985646, 876754, 876756),
                          TotOtherState = c(564534, 765645, 786756, 764534, 543423, 231234, 763423,
                                            567876, 876345, 453675, 876754),
                          StateCode = c("null", "null", "null", "null", "null", "null", "null",
                                        "null", "null", "null", "null"),
                          CID = c("c01", "c02", "c03", "c04", "c05", "c06", "c07", "c08",
                                  "c09", "c10", "c11")
          )
          
          p <- plot_ly(sp3, x = sp3$BlockName, y = sp3$TotRaj, type = 'bar', name = '',key = ~paste(sp3$DID,sep = ",",sp3$CID), source = 'link1',marker = list(color = '#537ec5')) %>%
            add_trace(y = sp3$TotOtherState, name = 'other-state',marker = list(color = '#57007e')) %>%
            layout(yaxis = list(title = 'sonography block_level'),xaxis = list(title = 'Block name'), barmode = 'group',title='')}
        else{
          return(NULL)
        }
        
      })
      
      output$plott4 <- renderPlotly({
        d<-event_data('plotly_click',source = 'link1')
        if(is.null(d)==F){
          sp4<-data.frame(stringsAsFactors=FALSE,
                          DID = c(101, 101, 101, 101, 101, 101, 101, 101, 101, 101, 101),
                          DistrictName = c("a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a"),
                          BlockName = c("aaa", "aaa", "aaa", "aaa", "aaa", "aaa", "aaa", "aaa",
                                        "aaa", "aaa", "aaa"),
                          CenterName = c("abc", "bac", "dac", "efc", "ghc", "hic", "jkl", "mnl",
                                         "lkj", "ghj", "kjh"),
                          TotRaj = c(2345, 2343, 6754, 7656, 8767, 6756, 4534, 5678, 6756,
                                     5434, 9876),
                          TotOtherState = c(4532, 2345, 3456, 4567, 9845, 9876, 6756, 5645, 4534,
                                            5645, 7865),
                          StateCode = c("null", "null", "null", "null", "null", "null", "null",
                                        "null", "null", "null", "null"),
                          CID = c("c01", "c02", "c03", "c04", "c05", "c06", "c07", "c08",
                                  "c09", "c10", "c11")
          )
          
          
          p <- plot_ly(sp4, x = sp4$TotRaj, y =sp4$CenterName , type = 'bar', name = 'rajasthan',key = ~paste(sp4$CID, sep = ""),source = "hey",
                       marker = list(color = '#1a3e59',
                                     line = list(color = 'rgba(246, 78, 139, 1.0)'))) %>%
            add_trace(x = sp4$TotFormFOtherState, name = 'other-state',
                      marker = list(color = '#baabda',
                                    line = list(color = 'rgba(58, 71, 80, 1.0)'))) %>%
            layout(barmode = 'stack',
                   xaxis = list(title = "Centre Data"),
                   yaxis = list(title ="Centre Name"))}
        else{return(NULL)}
      })
      
      output$plott5 <- renderPlotly({
        s <- event_data("plotly_click", source = "linkhere")
        req(!is.null(s))
          
          sp5<-tibble::tribble(
                   ~DID, ~DistrictName, ~BlockName, ~CenterName, ~TotRaj, ~TotOtherState, ~StateCode,   ~CID, ~StateName,
                 "null",        "null",     "null",       "nul",   "nul",           4532,          1, "null",    "alpha",
                 "null",        "null",     "null",       "nul",   "nul",           2345,          2, "null",     "beta",
                 "null",        "null",     "null",       "nul",   "nul",           3456,          3, "null",     "gama",
                 "null",        "null",     "null",       "nul",   "nul",           4567,          4, "null",    "theta",
                 "null",        "null",     "null",       "nul",   "nul",           9845,          5, "null",      "abn",
                 "null",        "null",     "null",       "nul",   "nul",           9876,          6, "null",      "mnb",
                 "null",        "null",     "null",       "nul",   "nul",           6756,          7, "null",      "vbg",
                 "null",        "null",     "null",       "nul",   "nul",           5645,          8, "null",      "hjg",
                 "null",        "null",     "null",       "nul",   "nul",           4534,          9, "null",      "klj",
                 "null",        "null",     "null",       "nul",   "nul",           5645,         10, "null",      "ghj",
                 "null",        "null",     "null",       "nul",   "nul",           7865,         11, "null",      "jhg"
                 )
    
          
          layout <- list(
            font = list(size = 12),
            title = " District-Wise",
            xaxis = list(title = "district"),
            yaxis = list(title = "records",automargin = TRUE)
          )
          
          p <- plot_ly(sp5, x = sp5$StateName, y = sp5$TotOtherState, type = 'bar', name = '',source = 'link3',key = ~paste(sp5$DID, sep = ""),marker = list(color = COL))
            
      })
      
      
      output$plott6<-renderPrint({
        s<-event_data("plotly_click",source = "link1")
        if(length(s)==0)
        {
          "CLICK ON BARCHART TO PRINT THE VALUES!"
        }
        else{
          as.list(s)
        }
      })
      
    }
    
    
    
    runApp(list(ui = ui, server = server), launch.browser = TRUE)

screenshots to make it understand better...
single

single2

i am using the stored procedure from ms SQL server for this purpose but created it the simplest way possible to make it run on other's machines. as shown in the images, how to break the key value into two values and store them in two different variables. how to split and store them.

the only thing that i need to know to split or break down sp3$DID and sp3$CID and store them two different variable
key = ~paste(sp3$DID,sep = ",",sp3$CID)

i have given the entire scenario just to give complete exposure to this issue but the main target to split it and store to the variable, everything else is superfluous what i wanna achieve

siddharth_sharma · November 19, 2019, 11:34am

how to break this and store into variable ,that's all i wanna know,only this one line of code,everything else is unnecessary ,i have attached that just to understand the scenario,tryna make it more specific to save time of others that would be consumed by reading superfluous info and unnecessary details,just tryna focus on specific line which is given below to avoid any sort of confusion due to lot's of info, might create confusion to absorb the problem quite well

key = ~paste(sp3$DID,sep = ",",sp3$CID)

looking for output like this...

*dynamic content*...
k1<-sp3$DID
k2<-sp3$CID

how statically it will look like...

k1<-101
k2<-c07

using the split function or something like that i am unaware of what that is

so when i check using verbatimtextoutput given above it must be there only like that instead of
key = ~paste(sp3$DID,sep = ",",sp3$CID),given again in picture dipicted below,and value of both k1 and k2 would be as per the bar-chart clicked ,it is dynamic ,there will be different value each time bar-chart is clicked

single2

siddharth_sharma · November 25, 2019, 5:00am

this is how we can achieve the key concept with ms sql server sp.

 output$plot <- renderPlotly({
 d<-event_data('plotly_click',source = 'link1')
 if(is.null(d)==F){
    m<- unlist(strsplit(d$key, split=","))
    v<-list(k1<-m[1],k2<-m[2])
qrydd=paste("exec db..[sp]",k1,",",k2,",'','','','4','2','','','01/01/2010','31/12/2017' ")
    qrydd
    dtd <- sqlQuery(dbcnd,qrydd)
    dtd <- data.frame(dtd)
    dtd
    closeAllConnections()

system · December 2, 2019, 5:00am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.