At my company, we use an R studio code that we run once a week and it connects to Domo (analytics platform). I have had zero issues with this code up until the past couple months. It has always run perfectly fine - slightly slow, but fine. Now, it starts to run but will never complete. Will sit halfway through the code for hours, the entire workday even. I have had luck with it finishing once or twice randomly now, but I try everyday and 9/10 times will not work. it stops almost at the very end with
i <- i+1
}
Code:
#rm(list=ls())
#Sys.time()
library("dplyr")
library("devtools")
library("DomoR")
Added line below to allow replace_ds() to work properly in parsing date types
library(lubridate)
DomoR::init('bauercomp', '6891cb9e47ad827dea7b2a372c26af7747d961c67cea4311') # New R token
df <- DomoR::fetch('5ae8130f-ba85-4237-a67b-fc107e08d28d') # R Input data set
df_colnames <- colnames(df)
df$PartNum <- trimws(df$PartNum) # trims whitespace from both sides
df$PartRev <- trimws(df$PartRev)
df$ftype <- trimws(df$ftype)
df$ffromjob <- trimws(df$ffromjob)
df$ftojob <- trimws(df$ftojob)
df$ffromlot <- trimws(df$ffromlot)
df$ftolot <- trimws(df$ftolot)
df$fsokey <- trimws(df$fsokey)
df$ftoso <- substr(df$fsokey, 1, 6)
df$AbsQty <- abs(df$Quantity)
a <- data.frame(df[ , 3:14])
b <- data.frame(df[ , 1:2])
a[a == ""] <- "N/A"
df <- cbind(b,a)
df <- subset(df, ftojob=="N/A" | is.na(ftojob))
df <- subset(df, ftolot=="N/A" | is.na(ftolot))
df$TypeNo[df$ftype == "M"] <- 1 # to sort by descending order of type
df$TypeNo[df$ftype == "I"] <- 2
df <- df[order(df$PartNum , df$PartRev , df$Datasource, df$TransDate, df$TypeNo, df$ffromjob, df$fsokey),]
df$rownum <- seq.int(nrow(df))
newdf2 <- data.frame(df[0,])
i <- 1 # outer counter for Sales order
j <- 1 # inner counter for Job order
counter <- 1
sono_rows <- nrow(df)
while(i <= sono_rows){
df$ftolot[i] <- i
if(df$ftoso[i] != "N/A") {
if(df$Datasource[i] == 1) {
for(j in counter:i) { # For loop to ensure that loop only runs upto the SO issue date excluding jobs moved into inv afterwards
if(df$ffromjob[j]!="N/A" & df$PartNum[j] == df$PartNum[i] & df$PartRev[j] == df$PartRev[i] & df$AbsQty[j] > 0) {
if(df$AbsQty[i] > df$AbsQty[j]) {
df[seq(i+1, nrow(df)+1),] <- df[seq(i, nrow(df)),]
df[i,] <- df[i,]
df$ffromlot[i] <- 'OLDROW'
df$ffromlot[i+1] <- 'NEWROW'
df$AbsQty[i+1] <- df$AbsQty[i] - df$AbsQty[j]
df$AbsQty[i] <- df$AbsQty[j]
df$AbsQty[j] <- 0
df$ftojob[i] <- df$ffromjob[j]
newdf2 <- rbind(newdf2, df[i,])
counter <- j # so that the next iteration only needs to start at the current j value
sono_rows <- nrow(df)
break
} else {
df$AbsQty[j] <- df$AbsQty[j] - df$AbsQty[i]
df$ftojob[i] <- df$ffromjob[j]
newdf2 <- rbind(newdf2, df[i,])
counter <- j
sono_rows <- nrow(df)
break
}
}
}
} else { # mimicked transactions of Sales orders & jobs not in inventory yet
for(j in counter:sono_rows) { # loop limits extended so that jobs with a scheduled finish after the SO due date is also considered
if ((df$PartNum[j] != df$PartNum[i] | df$PartRev[j] != df$PartRev[i]) & j > i) {
break
} else {
if(df$ffromjob[j]!="N/A" & df$PartNum[j] == df$PartNum[i] & df$PartRev[j] == df$PartRev[i] & df$AbsQty[j] > 0) {
if(df$AbsQty[i] > df$AbsQty[j]) {
df[seq(i+1, nrow(df)+1),] <- df[seq(i, nrow(df)),]
df[i,] <- df[i,]
df$ffromlot[i] <- 'OLDROW'
df$ffromlot[i+1] <- 'NEWROW'
df$AbsQty[i+1] <- df$AbsQty[i] - df$AbsQty[j]
df$AbsQty[i] <- df$AbsQty[j]
df$AbsQty[j] <- 0
df$ftojob[i] <- df$ffromjob[j]
newdf2 <- rbind(newdf2, df[i,])
counter <- j
sono_rows <- nrow(df)
break
} else {
df$AbsQty[j] <- df$AbsQty[j] - df$AbsQty[i]
df$ftojob[i] <- df$ffromjob[j]
newdf2 <- rbind(newdf2, df[i,])
counter <- j
sono_rows <- nrow(df)
break
}
}
}
}
}
}
i <- i+1
}
DomoR::create(newdf2, name="R Output test", description="Testing with mimicked transactions")
DomoR::replace_ds('8c20ca1e-0d51-45bf-9772-e19caed19215', newdf2)