Data completion

[Luk88kk]

Hello all,
I have a problem with a workflow that I am not able to go through by myself.
Namely, I have a file (input file) which contains products with customs codes (8 and 10 digits) added to them. My task is to add a duty rate to each code.
The problem is that the file with customs rates is incomplete - so my task is to take the closest rate to it, e.g. if there is no code 8207506000 in the "Duties Import" file and the closest one is code 8207500000, it should be taken into the report.
I have already started working on this. However, I have no idea how to find the rates for the other codes (selected in the unique tool). I do it manually, however it is time consuming.
Could someone please help me with this.

[DataNath]

I'm a little confused as to the logic that can be applied here, especially when differentiating. In the sample you have provided, I can see instances where there are blank duty rates, but the closest codes to them have multiple duty rates - how would you determine which to use? If you can provide a clearer description of the issue and perhaps trim the example down to a smaller size where you can show the inputs and the desired outcome, I'm sure it'd be easier to arrive at a solution and get your issue sorted!

[kuoshihyang · Accepted solution]

1. Try passing your initial results into a union, then populate your missing custom code 10 -digit with goods code, then sort by your custom code 10 - digit field. 

2. Once that is done you will see records that have Product ID, Customer 8 and 10 digit but no Goods code, Measure Type, and Duty.  

3. Then all you will need to do is add in some Multi-Row Formula tools to populate the missing values for Goods Code, Measure Type, and Duty based on your criteria and values from either the previous records or follow up records.  

Below is a screen shot of Duty being populated using this tool. You can update the logic in the tool to align with you 'Matching'

criteria.  

Hope this helps!

[Luk88kk]

Your idea worked perfectly. Thank you very much for pushing my process further. Have a nice day.

[kuoshihyang]

My pleasure Luk88kk, cheers!