How to change values when two tables joined?

[posywang]

Hi,

I have two tables and the 2nd table left join the first one. My question is how to change value in Quality based on value in Product?

For example, if Product name starts with "C", change value of Quality to "Bad". Please let me know. Thanks.

Table A
Prod ID	Product	Type
1	Apple	Fruit
2	Banana	Fruit
3	Bike	Vehicle
4	Car	Vehicle
5	Cat	Animal

Table B
Type	Source	Quality
Fruit	USA	Amazing
Vehicle	China	Great
Animal	Mexico	Awesome

[swuehl · Accepted solution]

Maybe use a MAPPING approach:

Don't join - use Applymap instead

MAP:

MAPPING

LOAD Type, Source & '|' & Quality

FROM TableB;

LOAD

[PROD ID], Product, Type,

     If(Left(Product), 'C', 'Bad', SubField(ApplyMap('MAP', Type),'|',2) ) as Quality,

    SubField(ApplyMap('MAP', Type),'|',1) ) as Source

FROM TABLEA;

[posywang]

I added null() to formula since I do have some blank data but otherwise the solution is perfect! Thanks!