Iterative Loop from R to Alteryx

[sean_carr]

I am trying to recreate what is done below in R in alteryx. Some of these functions are not in the R tool. Specifically calling out a specific cell location to use in the calculation and change that location each iteration and how to create output files each interation.

x <- 1
NumRows <- 5
for(x in 1:NumRows){
Contract <- SECustomerDataUS$Customer Data Contract[x]
CombineDataFrameUS$NET <- as.numeric(format(round(CombineDataFrameUS$LIST * CombineDataFrameUS[,Contract],2),
nsmall = 2))
##select the columns to write to file
ColsToWrite <- select(CombineDataFrameUS, ACTIVE:HASDUPLICATE)
writeData(wb, "Parts Master", ColsToWrite, colNames = TRUE)
wbname <- paste("PARTS_MASTER_LINCOLN_ELECTRIC_", SECustomerDataUS$Customer.Name[x], "_",
SECustomerDataUS$Country[x], "_", MMDDYYYY, ".XLSX", sep = "")
saveWorkbook(wb, wbname, overwrite = TRUE)
write.Alteryx(discrep, nOutput = x)
x = x + 1

}

[DiganP]

@sean_carr There are a couple of ways on how you can recreate this. To my understanding, the R code is just selecting columns and writing the specific columns to a file. 

1) You can use the formula tool, with data prep/blending tools to replicate this

2) You can use an iterative macro. Here's a tutorial.

Hope this helps.

[sean_carr]

@DiganP I think that can work for the iterative part but I am not sure how to do the below code with the formula tool. It is pulling the contract type (string) in row 1 from one table and saving it as the variable contract. It is then pulling the column in another file with that contract name and multiplies that by the list price for all rows.

Contract <- SECustomerDataUS$Customer Data Contract[x]
CombineDataFrameUS$NET <- as.numeric(format(round(CombineDataFrameUS$LIST * CombineDataFrameUS[,Contract],2),
nsmall = 2)) 

[DiganP]

@sean_carr It would be a series of tools such as join, append fields, union, formula or multi-field formula.

1) Use multiple input tools bringing in the proper data.

2) Use the 'join' category tools. Append fields get the data as a Cartesian join or you can use the join tool to get the data side by side based on contract number?

3) You can use the formula tool to do the multiplication for all the rows.

Above is just an example, its hard to visualize without looking at sample data. Do you have a before/after sample table that the R code is producing that you can share?

[sean_carr]

Digan,

The data can not be joined together because the parts data has 20,000 rows where the customer contracts has 12. For each part there is a certain multiplier based on the type of part it is and the contract the customer has. Is there a way to run the full version of R in alteryx?

[DiganP · Accepted solution]

@sean_carr The R tool inside of Designer will let you run that script. Make sure you install the packages so you can reference it in the r script. 

Install R package KB article

Link to macro, streamlines the installation of R packages

A Cheat Sheet of Functions to Use in the R Tool

Hope this helps.