Multi-Row Formula to multiply one column value against fixed value.

[wjohns50]

Hi All,

I'm relatively new to Alteryx, so maybe this is simple, but I've been beating my head against the wall for 4 hours, so I'm calling for help.

I'm trying to create a Multi-Row Formula to multiply the values in column x against the value in Row 2 of column y. Multiplying column x against a specific value in a table would also work. Below is the example with results manually entered for device 1:

Data	# of Component 1	Component 1 Cost	# of Component 2	Component 2 Cost
Device 1	2	$200	2	$40
Device 2	5		1
Device 3	3		7
Device 4	4		1
Device 5	5		10
Device 6	9		6
Device 7	2		8
Device 8	1		1

Rate Table	Cost
Component 1 Cost/unit	$100
Component 2 Cost/unit	$20

I have 16 different component columns to assemble unique cost formulas that will be applied to 20,000+ rows, but most revolve around multiplying value x against value y, so this will be a good start in troubleshooting the others. Any help would be much appreciated!

[Emil_Kos]

Hi @wjohns50,

I tried to make it dynamic but I am not sure how to complete it from start to finish:

You will need to write 16 formulas... to make it work but it should be quite straightforward.

[TomWelgemoed · Accepted solution]

Hi @wjohns50 ,

I think you can get away with just an append here after you've calculated the cost of each item. I hope I've understood you're issue correctly, but I would simply calculate the cost per item, take that cost and it append it back to the data set for it to apply to each line. 

I attach the workflow as well. The only caveat is if you had more prices and only wanted to attach that to specific rows etc...then you may want to start looking at either a join or indeed a multi-row formula.

Hope this helps,

Tom

[TomWelgemoed]

Looks like @Emil_Kos & I agree on the append tool!

[wjohns50]

Got it! Let it be known on this day, you are my personal heroes! Now to start crunching on some of the if cell A contains B, enter value C from cell D problems for some of the other tables... 

[TomWelgemoed]

Hi @wjohns50 ,

Good luck with that! FYI, You can look at the Transpose tool (in the Transform section) and pivot the columns into rows so that you have something like this:

RecordID	Name	Value
Device 1	Column A - Quantity	2
Device 1	Column A- Value	200
Device 1	Column B - Quantity	1
Device 1	Column B - Value	50

Sometimes that allows you to be "smarter" with adding a new column in a formula tool, e.g. a new total by simply referring to the "Name" column, e.g.

If Name='Column A - Quantity'

then Value * x

elseif Name='Column B- Quantity'

then Value/x

else NULL()

endif

As an example. Depends on the use case of course, but I think either the above approach, the Multi row tool or the Multi field tool (when applying the same formula to update each field) are all tools you can consider when doing this kind of work.

Best of luck,

Tom