Calculations with dynamic columns based on cell value

[Krau5i]

Hello All,

I’m looking for a neat solution for the following:

I created a CurrencyKey in below table and I’m looking to divide the column Impact with a specific exchange rate based on the CurrencyKey. For example, when the CurrencyKey is MTD2023, I want to calculate -500 / 1.006. When the CurrencyKey is Q12023, I want to calculate -1,500 / 1.017, etc.

Is there a nice solution for dynamic column names? My workaround would be to filter on all possibilities separately and perform the calculation, but that will have to be changed over time.

Thanks,
Tobias

[sforesti]

Hi @Krau5i,

One way to accomplish this would be to use an Unpivoting node with the columns you want to divide the Impact column by specified as the Value columns and no columns included in the Retained section. You can then use a GroupBy node to get a distinct list of column names and division values. Then join this list - which will have the currency keys in the ColumnNames field and the division values in the ColumnValues field - to the original data set on CurrencyKey = ColumnNames using a Joiner node. You can then use a Math Formula node to divide the Impact field by the ColumnValues field.

For your reference, I’ve included a screenshot of how you’ll want to configure the Unpivoting node below:

[Krau5i]

@sforesti

For your reference, I’ve included a screenshot of how you’ll want to configure the Unpivoting node

Thanks a lot for this idea @sforesti ! It works perfectly and is so much more concise than my workaround!