Dynamic Calculated Column Creation Macro - provide parameters

[RhysWilliams]

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, 

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

total_sales_net| total_volume | group1_sales_net| group1_volume  | group2_sales_net | group2_volume

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

[Ben_H]

Hi @RhysWilliams,

I think it would be helpful if you could attach some input data for us, just something with mocked up data in an excel sheet would do.

Regards,

Ben

[RhysWilliams]

Hey

I've attached it,

I think I need examples of the formula tools "new column" parameter name being edited and then also examples of the formula bar being dynamically edited.

Thanks

[RhysWilliams]

I found this

https://sculptingdata.com/2015/05/28/hacking-alteryx-editing-xml-directly/

which details how to manually do this, I'll take a look at what I need to do in order to macro the xml generation then apply it in a formula tool.

Ill keep this post updated when I have the answer

[danilang · Accepted solution]

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

[RhysWilliams]

HI @danilang 

This works great thank you so much! 

[RhysWilliams]

Hi @danilang 

I'm finding a weird issue when the numberator and denom share _volume at the end it fails. It looks like the macro tries to concatenate the two but I cannot see where its doing this. So conversion_volume and test_volume fail with the error message conversion_test_volume. If I rename the columns so they do not share _volume I do not get the error.

Any ideas?

[danilang]

Hi @RhysWilliams 

Can you possibly post some sample data the show this issue?

Dan