Hello Gurus, This is a problem I've come across several times in the past but I've never been able to solve (always found a work around somehow) but I'm throwing in the towel and asking for help this time. How can I create a macro that will take in all records where a given condition is met. For example, given this data set: CompanySaleACME156Company & Co678ACME78ACME1865Company & Co235 How can my macro: * Take in all records where [Company] == "ACME" * Perform calculations X, Y, and Z * Create a file containing ONLY [Company] data (This part I know how to do) * THEN, take in all records where Company == "Company & Co" * Repeat steps 2 & 3 * Continue for each unique [Company] name in the table So essentially, loop over an undetermined number of [Company] names, and perform calculations X, Y, Z for each

Loop/Iterative/Batch Macro that takes in all records where <condition = TRUE>

Hello Gurus,

This is a problem I've come across several times in the past but I've never been able to solve (always found a work around somehow) but I'm throwing in the towel and asking for help this time.

How can I create a macro that will take in all records where a given condition is met. For example, given this data set:

Company	Sale
ACME	156
Company & Co	678
ACME	78
ACME	1865
Company & Co	235

How can my macro:

Take in all records where [Company] == "ACME"
Perform calculations X, Y, and Z
Create a file containing ONLY [Company] data (This part I know how to do)
THEN, take in all records where Company == "Company & Co"
Repeat steps 2 & 3
Continue for each unique [Company] name in the table

So essentially, loop over an undetermined number of [Company] names, and perform calculations X, Y, Z for each

Macros

Interface Tools

Iterative Macro

Accepted answers

LordNeilLord

Hey @abovino

I understand!

Ok, so yeah a batch macro could do the job but it maybe easier to use a multirow formula tool instead. In the attached example I am outputting a spreadsheet per "Category" so in the mutirow I have used the "group by" function to restart at every category.

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

Formulas.yxzp

All comments

LordNeilLord

Hey @abovino

My question is....why do you need a macro to do this?

Most of the tools in Alteryx have the ability to group and work with the data at a company level

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

abovino

Hi @LordNeilLord, thanks for the response.

The catch here is that my outputs are CSV files that contain excel formulas which are driven by the recordID (As seen here), and I need a separate file for each customer.

So my original solution was:

Use the formula tool to create a column with the absolute path and name of the file using the customer name (IE: "C:\absolute\path\" + [customer_name] + ".csv")
Output tool uses, "Take File/Table Name From Field" feature

Great! I have a file for each customer and all my excel formulas are in place ... Or so I thought. Here is my Alteryx formula to compose an Excel formula:

"=J" + [RecordID] + "*" + [PRICE-1] (Which when opened in Excel will appear as the following formula, "=J2*32.99"

So this method works fine for the first file that's created but the subsequent files still maintain the Alteryx RecordID in my composed formula. This will be the formula in the first row of the 2nd CSV file:

"=J32*28.99" but what I really need is "=J2*28.99"

I don't necessarily need to use a macro to solve this, but perhaps I've been looking at this problem for so long I have tunnel vision.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703