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.
Solved! Go to Solution.
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).
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