Renaming values in a field in script

[ziabobaz]

Hi!

I have a resulting Maintable with the following fields:

Channel

Source

Visits

I want to run a final cycle where i want to replace certain text information inside the Source field

Noconcatenate //replacing the original Maintable with new one

Maintable:

Load

Channel, Visits, // ho i have to repeat the names of all existing fields here? My actual field list is huge (if i need all of them, where can i get them as a list of values?

if (Source contains "facebook", 'facebook',

     if (Source contains "instagram", 'instagram', Source) ) as Source // This is what I am trying to achieve, what is the correct syntax?

Resident Maintable;

Thank you!

[micheledenardi]

Hi,

you don't need to repeat all fields during resident load.

The best way to achieve what you want is to create a mapping table with the source string and the new string:

ReplaceTextTable:
Mapping
Load
    SourceString,
    TargetString
Inline [
SourceString, TargetString
facebook, Facebook
face book, Facebook
instagram, Instragram
];

Rename field Source as Source_TMP;
Rename Table Maintable to Maintable_TMP;

Maintable:
NoConcatenate
Load *,
    Applymap('ReplaceTextTable',Source_TMP,Source_TMP) as Source
Resident Maintable_TMP;
Drop Table Maintable_TMP;

Drop field Source_TMP;

[christophebrault]

Hi,

Using preceding load is a good pratice to achieve this kind of things. You can do someting like :

Maintable:

LOAD *,

          If(SubStringCount(lower(Source_old),'facebook')=1,'facebook',

               If(SubStringCount(lower(Source_old),'instagram')=1,'instagram',Source_old)) as Source

;

LOAD Channel,

          Source as Source_old,

          Visits,

          other fields ...

FROM YourSourceFile ;

DROP FIELD Source_old;

Edit : You can also use wildmatch(Source_old,'*facebook*')=1 instead of the SubStringCount function

[ziabobaz]

1. facebook, Facebook 
2. face book, Facebook 
3. instagram, Instragram 

Thank you!

What if I don't know all possible values of 'facebook' beforehand? It can be written with numerous typos.

[ziabobaz · Accepted solution]

I ended up with a separate mapping table. It worked.

SourceMappingTable: 

NoConcatenate 

Load

source,

if(wildmatch(source,'*facebook*')=1,'facebook',

if(wildmatch(source,'*stagram*')=1,'instagram',source)) as source_clean

RESIDENT Main_table;