Subtract Columns from each other dynamically

Question

Hello Alteryx Forum,

I am trying to figure out a solution to this and was wondering if possible. Right now I have this data.

2024_05_132024_05_202024_05_27C_Value24722547257325021821178517931785

I would want to subtract "C_Value" from "2024_05_27", but also want the operation to work dynamically when the penultimate field name changes when the week changes. Is there any way to achieve this?

Thanks in advance

jdminton · Answer

Based on @leozhang2work 's suggestion, you could do something like this to make it dynamic.

Subtract Column.yxmd

danilang · Answer

Hi @bigdatadan23

Similar in concept to @tim-regas solution, but somewhat simpler in execution

This uses a Field Info tool to build a new name for the penultimate column which is changed to  __RenamedColumn__ by the first Dynamic Rename.  The formula tool subtracts C_Value from this and the second Dynamic Rename restores the original column names

Dan

SubtractFromPenultimateColumn.yxmd

leozhang2work · Answer

Given the first few columns are the same type, another way to go would be pivot long those.

Then apply your dynamic logic through a filter tool, join/append back the c_values.

It is easier to implement the logic on rows rather than columns, and you can pivot the results back to columns if it is required in that form.