Script: Rename a value from a field at LOAD

[fgirardin]

Hello,

I use data from our ERP to make tables. The script used is:

LOAD

"FIELD1",
"FIELD2",
"FIELD3";

SQL Select *
FROM STR.db."ITEMS"
Where COMPANY = 'RSM';

I have a field in particular that contains 2 values that I'd like to rename as the same 

Field = "Item_Status"      --  Values available are: ACT, OBS, BOMOBS

I would like to replace all values "BOMOBS" by "OBS"

What would be the best way to do it in my script ?

Thank you for your help

[atoz1158 · Accepted solution]

Hi

There are a few ways to do this below are two methods, method 1 is the quick and easy way if this is going to be the only value changed, method 2 allows for expansion of the number of values in that field to be changed just by adding them to the "Mapping" load. I have assumed that Field2 is the field required to be changed in my examples but you can change that as required.

Method 1

LOAD

FIELD1,
IF(FIELD2='BOMOBS','OBS',FIELD2) AS FIELD2,
FIELD3;

SQL Select *
FROM STR.db."ITEMS"
Where COMPANY = 'RSM';

Method 2

Item_Stat_MAP:
MAPPING LOAD * INLINE [
Was, Shouldbe
BOMOBS, OBS
];

LOAD

FIELD1,
ApplyMap('Item_Stat_MAP',FIELD2) AS FIELD2,
FIELD3;

SQL Select *
FROM STR.db."ITEMS"
Where COMPANY = 'RSM';

Regards

Adrian

[fgirardin]

Thank you, the method 1 works perfectly !

Best regards

Fabien