Hi, I’m trying to fill a value based on an existing record, but sometimes the matching value is above and other times it’s below. Is there a way to fill using the value from the key column regardless of whether the record is above or below? I tried using forward-fill and backward-fill separately, but it didn’t work.
Regards,
Miguel
You can do it like this:
fillupdown.transform (3.3 KB)
You could add a Verify step to ensure that ‘Shipment cost’ column in Unique doesn’t have any commas (which it will if there is more than 1 non-empty ‘Shipment cost’ value for each ‘Shipment number’ value).
just a hint, the fill up and down works one as intended if the shipment numbers are in a sequence. In case they are unsorted/unstructured in the table the lookup solution from @Admin gives a correct result in any case.
This works well as long as the same ‘ship number’ values are contiguous.
For example, this won’t give the desired result:
But you can get around that by doing a Sort by ‘ship number’ first.
Sure, though my answer addressed what msalz presented and asked; what was in front of us (as Smith said after the Brisbane Test*). If, as I inferred, the basics were not understood then we should not gloss over that before improvement.
If their case differed, then as usual we need good input and actual output examples.
Here is the modified version of Monotone94 solution, that works with your example and keep the record intact in their original position.
Transform file
Modified_Fillbothways.transform (4.3 KB)
It is difficult to know what the constraints are from an example. Is it always sorted by ‘Shipment number’? Could there be multiple ‘Shipment cost’ values for 1 ‘Shipment number’? I guess the best we can do is state our assumptions.
If you don’t mention the cricket, we won’t talk about Olympic break dancing. ;0)
Thank you all for your input. Just to add some clarification: sorting by shipment number works, but I realized that to get the correct and consistent order, I also need to sort by shipment number and shipment cost. It turns out that second step was the one I was missing.
Regards,
Miguel
I am sure even the English team would still find cause for offence at that mental association. 
