R: Run a loop from 4 vectors to output 4 data tables

Question

Everyone,

I have 4 vectors V1, V2, V3, V4 i want to run through a loop in an SQL query. Each vector is a list of numbers in this format ('8001', '8002', '8003') for my SQL query. If I do a vlist <- list(c(V1, V2, V3,V4)) it give me a list

> vlist
[[1]]
[1] "'84035','84040','84037','84013','84036','84030','84026','84016','84020','84012','84019'"
[2] "'84010','84039','84029','84031','84022','84032','84044','84034','84041','84042','84043'"

query

 #my code for the SQL query
vQuery <- infuse("SELECT TOP (1000) [PKID]
                           ,[Equipment]
                           ,[Order]
                           ,[ActStartDate]
                           ,[ActFinDate]
                           ,[ActStartTime]
                           ,[ActFinTime]
                           FROM [sapData].[dbo].[SAP_IW_47]
                           where Equipment IN ({{Vectors}}) 
                           and OrdCat = 'ZWAR'
                           and AcTyAct like '%TUT'
                           and ActStartDate BETWEEN '{{start}}' AND '{{end}}'"
                           , Vectors= #???, start = startdate, end = enddate) 
      # make it a data table
      VectorQuery <- queryDataHub(vQuery)
      vTable <- data.table(VectorQuery)

I am wanting to input the V1, V2, ... in the vQuery infused where Vectors= #??. Now What I am trying to do is have a possible for loop to run the code above, and when vTable code runs I have an output of 4 data tables V1table, V2 table, ..... Would a for-loop be best for this. I can copy and paste the entire code 4 different times, but would like to condense it down.

What is `infuse`? Is each vector really of numbers, or (as it appears) is each "vector" just a single string? Have you tried a `for` loop or `sapply`? — r2evans, Oct 10 '18 at 16:01
`sapply(vlist, function(v) infuse("SELECT ... IN ({{v}}) ...", v=v, ...))` — r2evans, Oct 10 '18 at 16:02
Infuse is from the R package infuser. https://cran.r-project.org/web/packages/infuser/infuser.pdf — BB.squared, Oct 10 '18 at 16:34
It's good practice to make questions fully reproducible, including non-base packages required to make the code work. In this case, I don't know that it's strictly relevant since all you're asking to do is create a string that is then sent to whatever mechanism you need. Regardless, some good refs for reproducible questions: https://stackoverflow.com/questions/5963269, https://stackoverflow.com/help/mcve, and https://stackoverflow.com/tags/r/info. — r2evans, Oct 10 '18 at 17:25

score 0 · Answer 1 · answered Oct 10 '18 at 16:18

Consider setting up a defined method for your process that receives a vector element. Then use lapply which is an iteration function that returns a list object with equal length as its input.

Unlike for loop, lapply avoids the bookkeeping task of initializing an empty list and assigning elements to it in each iteration which by the way is perfectly fine to do as well.

proc_query <- function(v) {
      vQuery <- infuse("SELECT TOP (1000) [PKID]
                           ,[Equipment]
                           ,[Order]
                           ,[ActStartDate]
                           ,[ActFinDate]
                           ,[ActStartTime]
                           ,[ActFinTime]
                           FROM [sapData].[dbo].[SAP_IW_47]
                           where Equipment IN ({{Vectors}}) 
                           and OrdCat = 'ZWAR'
                           and AcTyAct like '%TUT'
                           and ActStartDate BETWEEN '{{start}}' AND '{{end}}'"
                           , Vectors= v, start = startdate, end = enddate) 
      # make it a data table
      VectorQuery <- queryDataHub(vQuery)

      return(data.table(VectorQuery))
}

# LIST OF DATA TABLES
vTables <- lapply(vlist, proc_query)

Alternatively with for loop:

# INITIALIZE EMPTY LIST WITH DEFINED LENGTH
vTables <- vector("list", length(vlist))

for (i in seq_along(vlist)) {
   # UPDATE ELEMENTS
   vTables[[i]] <- proc_query(vlist[[i]])          # STILL CALLING DEFINED FUNCTION
}

R: Run a loop from 4 vectors to output 4 data tables

1 Answers1