How to multiply to multiple columns in one table

[louyang]

Hi there,

I would like to convert the data in Table 1 by multiplying the rates in Table 2, and output Table 3.

That is Group A multiply by rate 1.8%, Group B multiply by rate 2.5%, and Group C multiply by rate 7%. 

I try to join the two table using the 'Join' tool, which it doesn't have the multiply function, I then used the formula tool itself by entering the rate numbers. 

However the rates could be updated annually so I would like to keep it as a separate input data, so that I don't have to update manually in the formula tool.

Appreciate if you could suggest on this.

[Qiu · Accepted solution]

@louyang 
I think this is a typical use case of combination of Transpose and Cross Tab.

[flying008]

Hi, @louyang 

A trick for your case. 

Spoiler

Round(ToNumber([_CurrentField_]) * ToNumber(Replace(Right([_CurrentFieldName_], Length([_CurrentFieldName_]) - FindString([_CurrentFieldName_], '|') -1), '%', '')) / 100, 0.01)

BTW, thanks @Qiu  for your sample data.