Translate data conditionally without join

Nevi1211

Hello,

I'm trying to convert some string data based on a mapping table.

Mapping table :

Group 1	Party 2	Party 1
Zoo	lzrd	lizard
Animal	lzrd	lizard
Pet	lzrd	lizard

data set

ID 1	ID 2	Group 1	Group 2	Party 1	Party 2
turtle	turtle	Animal	Anml	lizard	lzrd

This is the data I'm trying to translate. Basically if group 1 = Animal and party 1 = lizard then convert party 2 from lzrd to lizard.

The reason why I don't want to use formula is because I want to change the mapping from the mapping table without touching the code. And the reason why I'm not using join is because the group1 column in the mapping table is sometimes empty, meaning I cannot join with the group1 field. This creates duplicated lines in that case If I just join party 1 and party2.

ID 1	ID 2	Group 1	Group 2	Party 1	Party 2	right_Group 2	right_Party 1	right_Party 2
turtle	turtle	Animal	Anml	lizard	lzrd	Zoo	lzrd	lizard
turtle	turtle	Animal	Anml	lizard	lzrd	Animal	lzrd	lizard
turtle	turtle	Animal	Anml	lizard	lzrd	Pet	lzrd	lizard

Ultimately, I would like to know if there is a way to convert the party 2 column depending on the value of Group1 and party1 using the mapping table and without a join tool ?

Many thanks

MelGibson

I am not sure I fully understand your ask. Your mapping table is something you will manually own? you could do a find and replace and append the mapping table to the data set based on either a field OR you could create a unique field on both sides and use that with your find and replace (i.e. Group 1 + Party 1) then after you join have a formula for party  2 that states if !isnull([appended field name])  then [party 1] else party 2 endif. Not sure if that is what you are looking for.