Multiplying two tables

anamik72

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

grossal

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.

2020-05-23-Multiplying two tables.yxmd