Multiply multiple columns by a respective number

[dpowers]

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?

[ArtApa]

Hi @dpowers - This is what you can try:

[Qiu]

@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 · Accepted solution]

Dynamic replace could be another option.

Use the field info tool and a standard find/replace to determine what multiplier to use on which field, and then a dynamic replace to apply those formulas to each field.

[Qiu]

@SPetrie 
This looks nice.
I am always not quite undersanding the Dynamic Replace, this is good sample. Thanks.

[SPetrie]

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. :)

[dpowers]

@SPetrie and @Qiu thank you for the quick replies. I'll try SPetrie's option first and report on its success!

[dpowers]

@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