I'm banging my head against the wall here.
I have a very simply flow. I take in a CSV (granted its 24 million rows) and put it into an R script tool. No matter what I do I keep getting:
R (2) Caught an Error in WriteYXDBStreaming()
R (2) Unknown field type: Unknown
I have put a select tool infront of the R tool and unselected the "Unknown" field. I have also limited the CSV to only 10k rows to make sure it wasn't a volume issue.
My R script is as follows:
library(lubridate)
library(plyr)
#What did UTAS say i months ago about j months from today
#AD <- read_csv("~/Documents/Demand/AggregatedDemand.csv",
# col_types = cols(DEMAND_DATE = col_date(format = "%Y-%m-%d"),
# MATL_ID = col_character(), SNPSHT_RUN_ID = col_datetime(format = "%Y-%m-%d %H:%M:%S")))
AD <- read.Alteryx("#1", mode="data.frame")
AD$PLANT_ID <- as.character(AD$PLANT_ID)
AD$MATL_ID <- as.character(AD$MATL_ID)
AD$DEMAND_DATE <- as.Date(AD$DEMAND_DATE)
AD$SNPSHT_RUN_ID <- as.POSIXct(AD$SNPSHT_RUN_ID)
AD$SUM_DEMAND_QTY <- as.numeric(AD$SUM_DEMAND_QTY)
AD$DEMAND_TYPE_CD <- as.character(AD$DEMAND_TYPE_CD)
#AD$IS_PAST_DUE_IND <- as.factor(AD$IS_PAST_DUE_IND)
#format SNPSHT_RUN_ID into a date for alteryx sillyness
#AD$SNPSHT_RUN_ID = ymd(AD$SNPSHT_RUN_ID)
#Would current date need to be SNPSHT_RUN_ID - 1 day?
current_date <- max(AD$SNPSHT_RUN_ID)
#i months ago ## 0 indicates current forecast from today
months_ago_list <- c(0,1) #,3,6) you cant do beyond 3 at this point , we dont have the data
#j months ahead
months_ahead_list <- c(1,3,6,12)
final = NULL
for (i in months_ago_list) {
month_ago = current_date - months(i)
for (j in months_ahead_list){
month_ahead_end = current_date + months(j)
#
# #i month ago UTAS said the demand for today plus j month isthe sum of the demand qty by each material
# #Previous Forecast
PF<- subset(AD, SNPSHT_RUN_ID == month_ago)
PF<- subset(PF, DEMAND_DATE < month_ahead_end)
PF<- subset(PF, DEMAND_DATE >= current_date)
PF<- ddply(PF, "MATL_ID", summarise,i_month_ago_j_month_of_demand = sum(SUM_DEMAND_QTY))
#
#
# ## Current Forecast , need this for 1, 3, 6, 12 months
# #i=0
CF<- subset(AD, SNPSHT_RUN_ID == current_date)
CF<- subset(CF, DEMAND_DATE < month_ahead_end)
CF<- subset(CF, DEMAND_DATE >= current_date)
CF<- ddply(CF, "MATL_ID", summarise,zero_month_ago_j_month_of_demand = sum(SUM_DEMAND_QTY))
#
#
#
#
# #Merge the current forecast with the previous forecast
# ## What happens when the current forecast did not have this material in the previous forecast
merge<-join(CF, PF, by = 'MATL_ID')
# mergedf <- as.data.frame(merge)
# mergedf["i"] <- NA
# mergedf$i <- i
# mergedf[,j] <- i
# mutate(merge,newi = i)
# merge$i <- i
# merge$j <- j
# merge$SNP_SHT_DAY <- current_date
merge$i_month_ago_j_month_of_demand[is.na(merge$i_month_ago_j_month_of_demand)] <- 0
merge$zero_month_ago_j_month_of_demand[is.na(merge$zero_month_ago_j_month_of_demand)] <- 0
#
final<- rbind(final, merge)
#
}
}
#
# ############################################################## oNLY USE CODE ABOVE, NEED TO CREATE REST IN aLTERYX
#
#
#
final$churn <- with(final, (zero_month_ago_j_month_of_demand - i_month_ago_j_month_of_demand)/i_month_ago_j_month_of_demand)
final$churn2 <- with(final, (pmax(zero_month_ago_j_month_of_demand,i_month_ago_j_month_of_demand)-pmin(zero_month_ago_j_month_of_demand,i_month_ago_j_month_of_demand))/pmax(zero_month_ago_j_month_of_demand,i_month_ago_j_month_of_demand))
#merge<- mutate(merge, churn = ((zero.month.ago.j.month.of.demand - i.month.ago.j.month.of.demand)/i.month.ago.j.month.of.demand))
#merge<- mutate(merge, churn2 = (pmax(zero.month.ago.j.month.of.demand,i.month.ago.j.month.of.demand)-pmin(zero.month.ago.j.month.of.demand,i.month.ago.j.month.of.demand))/pmax(zero.month.ago.j.month.of.demand,i.month.ago.j.month.of.demand))
#merge2<- ddply(merge2, summarise,churn.sum = sum(churn))
# zero.sum = sum(zero.month.ago.j.month.of.demand),
# i.j.sum = sum(i.month.ago.j.month.of.demand),
# total.churn.pct = ((zero.sum - i.j.sum)/i.j.sum)*100)
#I think we need to build out a table like this. My thought is also, DT could eventually store a table
#like this then discard snapshots older than a certain amount of time.
#Plant ID / i / j / material.id / demand.type / snapsht.day / CF(0,j) / PF(i,j) / Churn = ((CF -PF)/PF) /
# In the future another column that can be added to this is Daily.Demade (Items that were actually shipped)
#Apurvas notes says to use Firm Sales Orders (Not Delivery)
# this table could thenbe used to forecast accuracy
#final[] <- lapply(final, as.factor)
final$RecordID <- as.character(final$RecordID)
final$PLANT_ID <- as.character(final$PLANT_ID)
final$MATL_ID <- as.character(final$MATL_ID)
final$SNPSHT_RUN_ID <- as.POSIXct(final$SNPSHT_RUN_ID)
final$DEMAND_DATE <- as.Date(final$DEMAND_DATE)
final$SUM_DEMAND_QTY <- as.numeric(final$SUM_DEMAND_QTY)
final$DEMAND_TYPE_CD <- as.character(final$DEMAND_TYPE_CD)
#final$IS_PAST_DUE_IND <- as.factor(final$IS_PAST_DUE_IND)
final$RecordID2 <- as.character(final$RecordID)
final$PLANT_ID2 <- as.character(final$PLANT_ID)
final$MATL_ID2 <- as.character(final$MATL_ID)
final$SNPSHT_RUN_ID2 <- as.POSIXct(final$SNPSHT_RUN_ID)
final$DEMAND_DATE2 <- as.Date(final$DEMAND_DATE)
final$SUM_DEMAND_QTY2 <- as.numeric(final$SUM_DEMAND_QTY)
final$DEMAND_TYPE_CD2 <- as.character(final$DEMAND_TYPE_CD)
#final$IS_PAST_DUE_IND2 <- as.factor(final$IS_PAST_DUE_IND)
final$i_month_ago_j_month_of_demand <- as.numeric(final$i_month_ago_j_month_of_demand)
final$zero_month_ago_j_month_of_demand <- as.numeric(final$zero_month_ago_j_month_of_demand)
final$churn <- as.numeric(final$churn)
final$churn2 <- as.numeric(final$churn2)
reallyfinal <- as.data.frame(final)
write.Alteryx(reallyfinal, 1)
It's not a terribly complicated script so I'm lost as to why Alteryx is behaving like this.
