Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thank you, the method 1 works perfectly !
Best regards
Fabien