Data clean up via script - replace values?

[jtkerlin]

I have two tables that I am trying to compare, the syntax in each file is different, but relates one to one.

Example:

Table1, anytime there is an instance of Production Code as "N", change the value to "New".

Table2, Production Code already shows "New".

So then I can compare all ID items to make sure the various values match.

TableName        ID        Production Code    Amount

Table1                   1          (N) New                          1000

Table2                   1          New                                 1000

[Lisa_P · Accepted solution]

A good practice for this is to have a mapping table that has old values and new values and you do a lookup to change all the old values to the new values.  

Or, if it just this one value, you could just change it as you load the table  ..

Load 

If([Production Code]="(N)", 'New', [Production Code]) as [Production Code],

..

from Table1 ...

[SergeyMak · Accepted solution]

Just to elaborate what Lisa meant by mapping table. For your case you'll need to place the following script before loading the data into your table where you want to update the value.
MAP_ProductionCode:
MAPPING LOAD * INLINE [
Key, Value
N, New
];

MAP [Production Code] using MAP_ProductionCode;

Another option would be to use ApplyMap. eg
ApplyMap('MAP_ProductionCode',[Production Code]) AS [Production Code]