Dynamic Calculated Column Creation Macro - provide parameters

Hi All

I'm hoping some one might have an idea how to help, I'll work through my example below.

I need to add a large number of repeated calculation columns onto my data set based on existing columns and I want to make this is dynamic and re-usable.

The existing columns are split into three groups where the column header is prefixed with the grouping. I want to be able to pass to a macro the column calculation required and new kpi name in a list. Its mostly divisions so I'll go with that for this example, but I could take it a step further and provide calculation type.

List data into macro would be as follows then also providing the data.

New Calc Column Name | numerator Column | denominator Column

e.g.

total_atv | "total_sales_net" | "total_volume"

group1_atv | "group1_sales_net" | "group1_volume"

group2_atv | "group2_sales_net" | "group2_volume"

The column headers are the same for each group apart from the text preceding the _ so I think I could go a step further and just pass the below to the macro, and the new column is prefixed with the group with the macro repeating 3 times for each grouping. Run 1 Prefix "total", run 2 prefix "group 1" ect..

atv | "sales_net" | "volume"

Any pointers would be appreciated,

Regex

Macros

Accepted answers

danilang

Hi @RhysWilliams

While I commend your enthusiasm, editing the xml of the workflow is overkill for the problem you have here. Here a batch macro based solution that does what your looking for.

The big container at the start of the workflow, just separates the data from the formulas. Once that's done, the next container transposes the data from one row per location to 3 rows, each with sales, volume and new_volume for each group. This is passed to the data input of the macro and formula fields are passed to control parameters.

inside the macro, there's a single formula tool that calculates numerator/divisor. The entire set of input rows is processed once for each row of data passed to the control parameter input. KPIColumn action tool modifies the connected formula tool to set the output field name to the current value. the Numerator and Denominator tools make the corresponding changes to the formula inside the formula tool. The select just removes unnecessary fields. The records for each iteration are unioned together before being passed back back to the calling workflow.

After building the field names and transposing the the results back to one row per location, these are joined to your original data to append the KPI fields to each location

Dan

Main.yxzp

All comments

atcodedog05

Hi @RhysWilliams ,

Your requirement is completely not clear. But i understood from your example is

This you data

total_atv | "total_sales_net" | "total_volume"

group1_atv | "group1_sales_net" | "group1_volume"

group2_atv | "group2_sales_net" | "group2_volume"

You want split it to columns

to follow below format and maybe a do a calculation

New Calc Column Name | numerator Column | denominator Column

Why dont you use a text to column or Regex tool to split.

Then again this as per my understanding. I might still not getting the bigger picture.

Please let me know how i can help further

RhysWilliams

Hi Quasar,

Apologies its a tricky one for me to explain and thank you for your time,

A small example of my columns is below (separate columns in Alteryx i'm using | to indicate different columns)

I need to create a large number of new columns for calculated KPI's, but the calculations follow a similar logic and need to be repeated at various aggregation layers.

I want a macro to loop through a list of kpi calculations parameters and apply that to my data set. We do a similar thing in coding where I would be providing parameters to a method/function.

KPI Column name = atv

KPI CALC Numerator Column = sales_net

KPI CALC Denominator Column = volume

List version-

atv | "sales_net" | "volume"

new_customer%| "volume" | "new_volume"

Then within the macro it loops for each list item 3 times, 1 for each group switching prefix each time.

I guess I want to know the feasibility using a formula tool but dynamically providing the new column name and the calculation that should occur within the formula tool?

Thanks

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335