Index/Match and multiply new values across an entire column

[dpowers]

Hi,

This is similar to a question I asked the other day (LINK), but there are a few new considerations that I'm having trouble building in.

I currently have individual store data that looks like this:

Store Number	Location	Region	Sales Volume	Revenue	Sales Cost	Salary Cost	Logistics Cost	...
1	A	North	#	#	#	#	#	#
2	A	North	#	#	#	#	#	#
3	B	West	#	#	#	#	#	#
...	...	...	#	#	#	#	#	#

I also have a key that is based on Region to update certain columns that looks like this:

Region	New Logistics Cost (per volume)
North	1.12
East	1.05
South	1.03
West	1.11

I need to allocate new values across a particular column based on the value in the Region column. Essentially the logic would be:

If, in a row, "Region" is "North", multiply North's "New Logistics Cost" by "Sales Volume"

In excel, I would do:

Sales_Volume * INDEX(New_Logistics_Cost, MATCH(Region))

The leading solution I'm considering is to:

1) Find/Replace to append on the new logistics cost,

2) Use a formula to multiply on volume and overwrite the Logistics_Cost column.

Is there an optimal way to do this in Alteryx? Through my searches I haven't found anyone with a nearly similar issue. I will have 10-50 million rows so I'm looking to be as resource efficient as possible.

Thank you!

[SPetrie]

Assuming you are using the dynamic replace option from the previous post, I think you can shoehorn in these calculations with minimal additional overhead.

Its a few tweaks to the previous method.

In my example, I wasnt sure if you had any existing values in New Logistics Cost so I just went with nulls.

The field needs to be in the dataset for a dynamic replace to insert formulas. So you may need to create the field if its not already in there. (a formula tool that creates the column with null values would work)

I also used column names that dont interfere with the previously setup column alterations.

Next, I created a text input with the required info that I want changed and applied.

In the previous example, the Boolean field was set to -1 (true) so it applied to every column that was called out.

This time around, we have multiple possibilities for a single column so they cant all be true. We use the region test to determine which formula is getting applied to that particular column and setup each of the possible scenarios.

I used 1-4 as my multiples to make it easier to see the result

Union those new rules in with the rules from the previous example and you should be good to go.