replace question

[amien]

REPLACE(text,value1,value2)

i want to use the REPLACE on a certain field, but i have a list of values that should be replace.

Can i use APPLYMAP for this, combines with REPLACE? someone has an example?

[swuehl]

You could apply Replace multiple times, one time for each value you need to replace.

Like

LOAD

...

Replace(Replace(Replace('ABC','A','First'),'B','Second'),'C','Third') as ReplacedString,

...

FROM ...;

[amien]

Thanks for your reply

Yes, i know .. but i have a lot of replacements to do. APPLYMAP would be much nicer and easier to maintain.

I'm really looking for a INLINE option

[swuehl · Accepted solution]

look into mapsubstring(), this might do what you want

edit from HELP:

mapsubstring( 'mapname', expr )

This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement. The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ( 'map1', 'A123' )	returns	' A<one>23'
MapSubstring ( 'map1', 'baaar' )	returns	' bXYZar'
MapSubstring ( 'map1', 'xaa1' )	returns	' bXYZ<one>'

[matt_crowther]

Try working on this basic script:

USA_Map:
LOAD * INLINE [
    Value, New_Value
    America, USA
    USA, USA
    US, USA
    United States, USA
];

Data:
LOAD Value,
ApplyMap('USA_Map',Value,Value) as New_Mapped_Value
FROM
[..\Data.xls]
(biff, embedded labels, table is Sheet1$);

First Load the Mapping - 'USA_Map' - 2 columns only, the first that matches the field value you want to replace and the second (called what you want) that contains the values you want to see. Once loaded you can move on and load your data as normal but then simply apply the map to the 'text' field.

Works for me and can be as large as required.

Hope that helps,

Matt - Visual Analytics Ltd

[amien]

@swuehl .. thanks .. just what i needed