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
Solved! Go to 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 :)
@harshendra One way of doing this with the batch macro
@harshendra
For dynamic columns, it is always effective to use the combination of Transpose and Cross Tab. 😁
Thanks all, I will try transpose and crosstab approach and update the topic.