Multiply multiple columns by a respective number
- 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. 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.
- Labels:
- Join
- Optimization
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@SPetrie
This looks nice.
I am always not quite undersanding the Dynamic Replace, this is good sample. Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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. :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@SPetrie and @Qiu thank you for the quick replies. I'll try SPetrie's option first and report on its success!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
