I have two tables. Second table provides weights which are to be multiplied with every line in Table 1. I am sharing desired inputs and output. Can you help? Thanks
Table 1 Input | ||
Customer | Line item | Amount |
A | Volume | 30 |
A | Revenue | 100 |
B | Volume | 50 |
B | Revenue | 150 |
Table 2 Input | ||
Channel | Channel Code | Weight |
Large | 1 | 50% |
Medium | 2 | 40% |
Small | 3 | 10% |
Table 3 Output | ||||
Customer | Line item | Channel | Channel Code | Amount |
A | Volume | Large | 1 | 15 |
A | Volume | Medium | 2 | 12 |
A | Volume | Small | 3 | 3 |
A | Revenue | Large | 1 | 50 |
A | Revenue | Medium | 2 | 40 |
A | Revenue | Small | 3 | 10 |
B | Volume | Large | 1 | 25 |
B | Volume | Medium | 2 | 20 |
B | Volume | Small | 3 | 5 |
B | Revenue | Large | 1 | 75 |
B | Revenue | Medium | 2 | 60 |
B | Revenue | Small | 3 | 15 |
Solved! Go to Solution.
Hi @anamik72,
here is a quick way to do this:
What happens:
- The Append Fields generates all combinations - make sure to allow all appends
- We then use a Formula to generate the new Amount:
We have to remove the % sign in the end, therefore I use Left and the Length of the string -1 to get every character except the last one. We need to use "ToNumber" to convert it to a number and use it for the multiplication. Off course we need to divide it by 100, because we want our % back.
- In the end I remove the Weight column with the Select Tool.
Workflow attached. Let me know if this solved your problem.