Vishnu Raghavan Vishnu Raghavan - 3 years ago 205
R Question

Unexpected output-For Loop

I Am running a nested for loop in R. I have created a dataframe called dataframe from an SQL query. My data are arranged as groups of 3 databases. The following

CampaignGUID- this has a column by means of which I refer to other columns

for (i in length(nrow(dataframe$CampaignGUID))){
sql1 <- paste("SELECT T.TenantLookupID, T.TenantShortCode,
B.BusinessUnitShortCode, C.CampaignShortCode
from TenantLookup AS T
JOIN BusinessUnitLookup AS B ON T.TenantLookupID =
JOIN CampaignLookup AS C ON C.TenantLookupID = B.TenantLookupID
where T.TenantID = '611c5d1c_f0bc_4bd4_b1c1_f6800d50fc34' AND
B.BusinessUnitID = 0
AND C.CampaignID =", i,sep="")
dataframe2<-dbGetQuery(jobdbconn, sql1)

Second part below

blasttable <- paste("rptcampaignblast_",(dataframe$CampaignGUID))

blasttable2<-gsub("-", '_', blasttable)
blasttable3<-sub(' ', '', blasttable2)

TenantLookupID1 <- dataframe2$TenantLookupID
TenantShortCode1 <- dataframe2$TenantShortCode
BusinessUnitShortCode1 <- dataframe2$BusinessUnitShortCode
CampaignShortCode1 <- dataframe2$CampaignShortCode

sql2 <- paste("select count(DISTINCT BINARY R1) from ", blasttable3)

blastcount<-dbGetQuery(reportconn, sql2)

sql3 <- paste("select count(DISTINCT BINARY R1) from
rptcampaignopentransaction where T1 ='", TenantShortCode1, "' AND B1 ='",
BusinessUnitShortCode1, "' AND C1 ='", CampaignShortCode1, "'")

opencount<-dbGetQuery(reportconn, sql3)

sql4 <- paste("select count(DISTINCT BINARY R1) from
rptcampaignlinkclickstransaction where T1 ='", TenantShortCode1, "' AND
B1 ='", BusinessUnitShortCode1, "' AND C1 ='", CampaignShortCode1,

linkclickcount<-dbGetQuery(reportconn, sql4)

sql5 <- paste("select count(DISTINCT BINARY R1) from
rpt_mailbounces.rptcampaignmailbounce where T1 ='", TenantShortCode1, "'
AND B1 ='", BusinessUnitShortCode1, "' AND C1 ='", CampaignShortCode1,

bounchcount<-dbGetQuery(reportconn, sql5)

delivered <- (blastcount-bounchcount)

DF2$reach= blastcount-bounchcount

DF2<-rbind(DF2$Bounced, DF2$delivered, DF2$reach,
DF2$blastcount,DF2$click, DF2$open, DF2$CampaignGUID)

} #for i

The final output of linkclickcount, bounchcount etc should be a list of values. The output is however a single value. I am not sure what the error is.

Answer Source

It's really hard to understand your code, however, here are a few issues I see:

  1. Your for loop control statement is doing for(i in length(nrow(df))){... You should try to evaluate length(nrow(df)) which returns just a length 1 vector. Therefore i evaluates to 1. To solve this, do:

    for(i in 1:nrow(df)){...

  2. You are not assigning your results of the query to a list. If you just re-run each iteration of the loop, it will overwrite your data. You'll need to do something like:

    query_results <- list()

    query_results[[i]] <- dbGetQuery(jobdbconn, sql1)

  3. Your SQL filter is taking the raw form of i (or just the value of 1) in:

    paste(...AND C.CampaignID =", i,sep="")

    You'll need to actually set the SQL filter to the campaignGUID by:

    paste(...AND C.CampaignID =", dataframe$CampaignGUID[i], ,sep="")

Overall, I would recommend evaluating each section of your code once manually and seeing the outputs before writing a for loop. Good luck

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download