Data completion

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.

Workflow.yxmd

Duties Import 01-99.xlsx

Inputfile.xlsx

Data Investigation

Accepted answers

kuoshihyang

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!

All comments

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

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.

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703