I have a dataset that has the following fields:
For every adjustment, I want to adjust the sales based on the adjustment by doing a reverse calculation for each record but only beginning from the date of the adjustment.
Example:
Below is an example of the original sample data and expected output: (if there's a way to keep the % of total in a single column for all adjustments, that would be ideal, given that there is no way to know how many adjustments a particular ID can have).
Solved! Go to Solution.
Hi @sheidari
This was a fun one to try and tackle. The attached workflow should get you what you are looking for. I used an append tool which might cause some issues if you have a much larger data set but this seems to work with the info you provided.
Hi @phottovy,
This works exactly as I need it but unfortunately the append tool is creating way too many records. My dataset is around 230K records so I let it run for about 10 minutes and it was up to 98 GB at 55% completion.
Thank you for taking a stab at it - I really appreciate the assist!
Any other way to solve for this more efficiently?
@arathirajeev Thanks for trying this one out - it's definitely a challenging one.
Yours works well but it stops short of calculating all the way through. The key is that the reverse calculation begin on the date of the adjustment but should continue going backwards until the earliest date.
Yep, we should get rid of the append tool. Attached is slightly modified version that uses the "Generate Rows" tool instead of the append tool. To get it to work, I had to convert your period column into a date field, then I calculate all of the dates that come before the adjustment row. This allows for a normal join instead of the append tool. Hopefully this runs faster.
Looks like it's still exponentially growing (up to 300 GB and still going). i switched it to a join tool and that seems to be working ok. it's dropping some records but looks like it should as expected.
THanks for the assist on this one!