Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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
Thank you!
What if I don't know all possible values of 'facebook' beforehand? It can be written with numerous typos.
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;