I have a table resulting from a sql query, so each time I run the query in R it will generate values for each category. I had a for loop for running this SQL query.
Result will be like this.
Example:
1st loop: NameAge
A 22
B 24
2nd loop: NameAge
C 25
D 26
3rd Loop NameAge
T 28
S 26
I need to Append this in a single tabel:
After Looping all data to in a single table: NameAge
A 22
B 24
C 25
D 26
T 28
S 26
I tried using rbind
df2<-rbind(df1,currTableDF)
but I am not getting desired result, any help would be greatly appreciated.
Instead of for loop you can use purrr::map_dfr that will create a dataframe in the end for you. Without code it's difficult to say more than this, but the change should be straightforward.
library("RODBC")
library(sqldf)
library(formattable)
library(qicharts2)
library(ggplot2)
library(R2PPT)
library(qcc)
library(RDCOMClient)
library(flextable)
library(data.table)
df2<-c('123','248')
df4<-c("Supplier2","Supplier1","Supplier3")
for (i in df2)
{
for (j in df4)
{
dbhandle <- odbcDriverConnect('driver={SQL Server};server=AAAA;database=TT;trusted_connection=true')
currTableSQL<-paste0("with tab1
as
(
Select * from Table1 where end_dt>=DATEADD(DAY,1,EOMONTH(getdate(),-6)) and t.id = '",i,"' and s.Supplier = '",j,"' and c.part='TT' and t.Type = 1")
currTableDF<-sqlQuery(dbhandle,currTableSQL)
print(currTableDF)
currTableDF$Week
# substring(currTableDF$Week,12)
class(currTableDF$Week)
df1<-data.frame()
df2<-rbind(df1,currTableDF)
# setDT(df2)
# setkeyv(df2,grep("index",names(df2),v=T))
# unique(df2)
# print(df2)
}
}
For each loop I am generating a dataframe called"currTableDF". I am trying to create empty dataframe named "df1" and now I need to load each iteration data from "currTableDF" to "df1" and name it as "df2" a new dataframe. It should have all values of each iteration.
Main thing I need is to update values of each iteration to a single dataframe.
As you can see, it's possible to generate 6 SQL queries and what you need to do is to change purrr::map2_chr to purrr::map2_dfr and uncomment actual SQL query.
Sure I will try the code. In meantime I used same "rbind" to solve this issue.
Its kind of declaring the empty dataframe out of the loop and use "rbind" to solve this.
Thank you very much for your effort. I think its "purrr::map" new one and a new learning. The main objective of this process is, I am trying to represent different Suppliers in a single chart.
I need to represent "Supplier1","Supplier2", "Supplier3" in a single chart with different color. Any idea....
I really don't understand why you need a for-loop for this, if you want to end up with just one dataframe, why you don't simply use the in operator in your sql query?, something like this
Select
*
from
Table1
where
end_dt >= DATEADD(DAY,1,EOMONTH(getdate(),-6)) and
t.id in ('123','248') and
s.Supplier in ('Supplier2','Supplier1','Supplier3') and
c.part='TT' and
t.Type = 1
This will give you all the combinations on a single table eliminating the need for iterate and bind.