"cap. fed","capital fed","capital federal", "sn martin", "san martin","st martin"

[#1]

i have something like this:

    City

1. Capital federal

2. capital Federal

3. cap. Fed

4. capital Nacional

5  San martin

6  St Martin

7  San Martin

8  San Jose

...

10.000 San Juan

I need an standard table whit only a name per city each city, like this:

1. Capital Federal

2. Capital Federal

3. Capital Federal

4. Capital Nacional

5  San Martin

6  San Martin

7  San Martin

8  San Jose

...

10.000 San Juan

Can anybody help me?

Thanks,

[MayilVahanan]

Hi,

     Try like this,

For Example:

C1:
Load * Inline
[
City
Capital federal
capital Federal
cap. Fed
capital Nacional
San martin
St Martin
San Martin
San Jose
];

C2:
Mapping Load * Inline
[
ReNameCity,City
cap. Fed,Capital Federal
Capital federal,Capital Federal
capital Federal,Capital Federal
St Martin,San martin
];

Load *,ApplyMap('C2',City) as ReNameCity Resident C1;

Hope it helps

[jonathandienst]

Hi

This is my suggestion. Use a map similar to the previous post, but first process the data to remove spaces, periods (and any other punctuation) and convert to lower case. This means that the mapping table will be much more flexible.

This is the script:

MapCleanup:

Mapping LOAD * Inline

[

               Lookup, Value

               capitalfederal, Capital Federal

               capfed, Capital Federal

               capitalnacional, Capital Nacional

               sanmartin, San Martin

               stmartin, San Martin

               sanjose, San Jose

];

Data:

LOAD

          Data, 

          ApplyMap('MapCleanup', lower(purgechar(Data, '.- '))) As CleanData

Inline

[

               Data

               Capital federal

               capital Federal

               cap. Fed

               capital Nacional

               San martin

               St Martin

               San Martin

               San Jose

];

See the attached for this in action.

Regards

Jonathan

[bgerchikov]

Hi Ivan,

Try this:

MapCity:

mapping load * inline [
From, To

cap., Capital

Fed, Federal

St, San

]

;




CityOrigin:

LOAD * Inline [

City

Capital federal

capital Federal

cap. Fed
capital Nacional

San martin

St Martin

San Martin

San Jose

]

;



CityNew:

NoConcatenate LOAD

capitalize(ApplyMap('MapCity',SubField(City,' ',1))&' '& ApplyMap('MapCity',SubField(City,' ',2))) as City

Resident  CityOrigin;

drop Table CityOrigin;