R Output Failure

[afreseman]

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.

[DylanB]

Glad to hear that did it. Yeah. That's a fairly nonsensical error message (and I think it can apply to more than just this case).

[DylanB]

I really have no clue why that's happening, but I changed the line

final["SNP_SHT_DAY"] <- as.character(final["SNP_SHT_DAY"])

to be

final["SNP_SHT_DAY"] <- sapply(as.character, X = final$SNPSHT_DAY)

and that seems to work