Reatribute values to other rows based on criterias

[max_hfr]

Hi guys,

I am not able to share the real data with you but I came up with a simple example which matches the porblem I am facing on the real data.

So as you can see there are two Id's.

ID (Custommer) : unique key

ID2 (Seller) :  not a unique key

what I need to get is this : 

To put it in words I have in my data some ID that are null. However, ID2 is always populated.

I am using the data at a month level.

Therefore in order not to loose the count of potatoes when the ID is empty, I want to reattribute those potatoes to the latest fully populated line for that month (or one of the lines). Also I have to reattribute it to the right ID2. 

So for exemple in the screenshots.

Seller y32 sold potatoes to 2 custommers in December 2019. However 1 of the custommers left the database so we have one empty ID plus the ID x2.

So for that month I will act like y32 sold all of its potatoes to the other custommer x2 in order to have no ID null. 

You will find the example attached,

Thank you guys in advance for your help

[ponraj · Accepted solution]

Here is the sample workflow. hope this is helpful. 

[danilang · Accepted solution]

Hi @max_hfr 

Here's another way to do it

Use a formula to get the month and year for all dates.  The year is important so your solution will work in multi-year situations  Split between IDs and no IDS.  Select the 1st records for the records with ids and join the sum of the extra potatoes to these records.  Add the extras to the count and join all the records back up. 

Dan