Hi. I have a dataset where I need to scale every column's values by a multiplier so that it ties to other data.
Currently I have two sources.
1) Primary data which looks like this:
Store Number | Location | Revenue | Costs | Marketing Expense | Discount | ... |
1 | A | # | # | # | # | ... |
2 | B | # | # | # | # | ... |
3 | C | # | # | # | # | ... |
2) A multiplier key which looks like this:
Revenue | 0.97 |
Costs | 0.88 |
Marketing Expense | 1.12 |
Discount | 1.07 |
... | ... |
I'm looking for the least resource intensive way to multiply all of the Revenue column by the Revenue multiplier, all of the Costs column by the Costs multiplier, and so on (30 columns in total).
I've seen solutions which may work using Append Fields, but my data is millions of rows by 30-40 columns, so I'm afraid that would balloon my data severely.
Through my searches I haven't found anyone with a similar issue. Do you have any thoughts on a solution?
Solved! Go to Solution.
@dpowers
I tried an quite not-so-normal by bring the multiplier key to the column name.
But need to make the the Field Name and order to be consistent for two data streams.
@SPetrie
This looks nice.
I am always not quite undersanding the Dynamic Replace, this is good sample. Thanks.
Thanks @Qiu
I recently started using it more and am still getting used to it myself. Its my newest hammer, and every problem looks like a nail now. :)
@SPetrie and @Qiu thank you for the quick replies. I'll try SPetrie's option first and report on its success!
@SPetrie this was exactly what I needed, thank you!
To anyone referencing this in the future, be sure to download his example workflow to see the configurations at each step