Iterative Loop from R to Alteryx

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

}

R Tool

Accepted answers

DiganP

@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.

All comments

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))

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335