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) IDFirst NameLast NameCodeCountryCosts1JohnWillis1234USA$200002JackDiamond4567USA$350003DallasFranklin1478Mexico$250004JulieDouglas4246Ireland$2500 Table 2 (New Report) CodeCountryTaxes1234USA$5004567USA$5501478Mexico$3004246Ireland$600 Final report should include Table 1 with the addition of the field "Taxes". Any help would be great. Thanks

Add field to existing dataset based on non-unique ID

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

Workflow

Accepted answers

aatalai

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

All comments

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703