Repetitive formula to create new columns

[harshendra]

Hello folks!

I am currently using Formula tool to generate new rows that have repetitive formula but using different columns. Is there a way that I do not need to re-write the same formlua for many columns? 

I have two sources of data.

Source Data: that has product amounts by each ID.

ID	Product_123_Amt	Product_ABC_Amt	Product_XYZ_Amt
AA	14041.58	28735.43	93896.96
BB	16453.21	62606.2	28602.4
CC	83453.49	18025.12	54810.77
DD	4219.11	48773.5	27987.05
EE	28193.88	15343.13	95670.15
FF	73438.03	79550.61	67179.56
GG	10098.67	96634.93	27446.76
HH	46252.51	53855.29	48126.03
II	58690.78	8955.56	55635.67
JJ	94182.58	97780.48	11577.28

Actual Amount: which should be the actual total amount for each product.

Actual_Product_123_Amt	Actual_Product_ABC_Amt	Actual_Product_XYZ_Amt
46105.45	11162.67	10479.54

I need to pro-rate the amount by ID in Source Data such that the totals become equal to Actual Amount.

This involves two steps.

1. To get the total from Source Data (Sum_Product_123_Amt, Sum_Product_ABC_Amt, SUM_Product_XYZ_Amt)

2. Apply the formlua Final_Product_Amt = [Product_Amt]/[Sum_Product_Amt]*[Actual_Product_Amt] 

This gives me the below output which is correct.

ID	Product_123_Amt	Sum_Product_123_Amt	Actual_Product_123_Amt	Final_Product_123_Amt	Product_ABC_Amt	Sum_Product_ABC_Amt	Actual_Product_ABC_Amt	Final_Product_ABC_Amt	Product_XYZ_Amt	Sum_Product_XYZ_Amt	Actual_Product_XYZ_Amt	Final_Product_XYZ_Amt
AA	14041.58	429023.84	46105.45	1508.991586	28735.43	510260.25	11162.67	628.6284742	93896.96	510932.63	10479.54	1925.883943
BB	16453.21	429023.84	46105.45	1768.159669	62606.2	510260.25	11162.67	1369.599828	28602.4	510932.63	10479.54	586.6526765
CC	83453.49	429023.84	46105.45	8968.407701	18025.12	510260.25	11162.67	394.3251826	54810.77	510932.63	10479.54	1124.202337
DD	4219.11	429023.84	46105.45	453.4106197	48773.5	510260.25	11162.67	1066.989806	27987.05	510932.63	10479.54	574.0314725
EE	28193.88	429023.84	46105.45	3029.8818	15343.13	510260.25	11162.67	335.65283	95670.15	510932.63	10479.54	1962.253152
FF	73438.03	429023.84	46105.45	7892.086883	79550.61	510260.25	11162.67	1740.282939	67179.56	510932.63	10479.54	1377.893767
GG	10098.67	429023.84	46105.45	1085.263058	96634.93	510260.25	11162.67	2114.026782	27446.76	510932.63	10479.54	562.9497949
HH	46252.51	429023.84	46105.45	4970.569438	53855.29	510260.25	11162.67	1178.161203	48126.03	510932.63	10479.54	987.0942406
II	58690.78	429023.84	46105.45	6307.259808	8955.56	510260.25	11162.67	195.9156351	55635.67	510932.63	10479.54	1141.121539
JJ	94182.58	429023.84	46105.45	10121.41944	97780.48	510260.25	11162.67	2139.08732	11577.28	510932.63	10479.54	237.457077

So, I am able to achieve the end results with Formula tool. Now here I am showing three products, but in reality I have around 50+ products. As of now I am creating the Final column with the help of Formula tool. But I will need to repeat the formula 50 times and the products will vary. Is there a way using which I can do what I am doing without the need of writing the formula 50 times?

Thanks,

Harshendra

[TimN]

This seems to work but I struggle with Dynamic Replace and if it really fits in this use case.  

[abe_ibanez · Accepted solution]

Hello, 

When I have to work with many columns that have the same structure, I normally transpose the data to allow me to more easily manipulate all at once. 

After you are done with your calculations, you can cross-tab it back to the original format. 

Here is an example: 

The top input is the current data. 

The bottom input is the total it should add up to. I used a dynamic input tool to get rid off the "Actual_" so that the fields would match. 

After the first transpose on the top data stream, I used a summary tool to calculate the current sums. With that, I appended what the actual amount should be to calculate the weight for the scalar. I then joined that weight to the transposed data to the corresponding product. After scaling the values with that weight, I cross-tabbed the data back into its original form. 

This should work for any number of products. 

I hope this helps :) 

[binuacs]

@harshendra One way of doing this with the batch macro

[Qiu · Accepted solution]

@harshendra 
For dynamic columns, it is always effective to use the combination of Transpose and Cross Tab. 😁

[harshendra]

Thanks all, I will try transpose and crosstab approach and update the topic.