Multi-Row Formula to multiply one column value against fixed value.
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Looks like @Emil_Kos & I agree on the append tool!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
