Add field to existing dataset based on non-unique ID

[walkerj7]

I have a large report that I run daily and I need to add a field based on non-unique IDs.  I've tried running a join based on Code and Country but I get no output.

Table 1 (Existing Report)

ID	First Name	Last Name	Code	Country	Costs
1	John	Willis	1234	USA	$20000
2	Jack	Diamond	4567	USA	$35000
3	Dallas	Franklin	1478	Mexico	$25000
4	Julie	Douglas	4246	Ireland	$2500

Table 2 (New Report)

Code	Country	Taxes
1234	USA	$500
4567	USA	$550
1478	Mexico	$300
4246	Ireland	$600

Final report should include Table 1 with the addition of the field  "Taxes".  Any help would be great.

Thanks

[aatalai]

@walkerj7 are the code in the same format for both tables, i.e are they both numeric or both strings, that could cause you issue with their being no outputs from the join

[walkerj7]

Yes, all fields match data types.

[aatalai]

are they all in the same order? if so you might be able to join based on position and use that as a work around

[aatalai · Accepted solution]

otherwise try using a data cleanese for both to ensure there is no trailing spaces/duplicate white spaces etc and that might help as for a join to work you need exact matches

[walkerj7]

This is a large dataset with thousands of rows with combinations of code and country.  The columns are in the same order in each data set but the combinations are not.

[aatalai]

@walkerj7 I meant are the rows the same, in your example it seemed to be a perfect match between the 2 reports, or again with the thousnds or rows this wouldn't be the case?

[alexnajm]

Your original solution would be what I would’ve done. Can you post a workflow showing your configurations? There should be an output from the J anchor based on what you described. 

otherwise it would also be useful to see the expected output so we can visualize what is wanted

[walkerj7]

After using the data cleanse tool I able to join the fields properly.  Thanks for your help.