Contains and Replace

[Anonymous]

Hi, I've loaded my excel table and I need assistance with adding a contains and replace into my script.  For example I have a description column in my excel table that contains commentary.  I need to search for keywords from this column and replace it with a name.

So..for example, my 'Description' column looks like

1) Summary: Ticket Group: US_Team1 - on this date, xxx

2) Summary: Ticket Group: Asia_123Team - on this date, xxx

I need my script to search this Description column where if 'US_' exists then I need this new field to display Team 1 or if 'Asia_' exists I need this new field to display 123Team.  It needs to extract keyword between US_ and - or Asia_ and -

Hope this makes sense. Thank you.

[#2]

It might not be super scalable, but you could try something like TextBetween() with nested if statements in your preceeding load. It'd require everything to follow the format you listed though, specifically in that the text you want to extract falls between the "US_" and " - " characters. But it might be a starting point, anyways!

LOAD Column1,

If(WildMatch(Column2,'*US_*'),TextBetween(Column2,'US_',' - '),

if(WildMatch(Column2,'*Asia_*'),TextBetween(Column2,'Asia_',' - '),)) as [Team Name]

INLINE [

Column1, Column2

1,Summary: Ticket Group: Asia_123Team - on this date, xxx

2,Summary: Ticket Group: US_Team1 - on this date, xxx

];

Edit: In thinking more about this, I supposed you could do without the nested if statements that check for the country name. That seems kind of pointless I guess. If you know that there's only one _ character in the string and it always follows the country name, then you'd just do something like this:

TextBetween(Column2, '_', ' - ')as [Team Name]

[MarcoWedel]

Hi,

one solution could be:

tabDelimiters:
LOAD * Inline [
Delimiter
Africa
Americas
Asia
Australia
Europe
US
];

mapDelimiters:
Mapping LOAD RecNo(),*
Resident tabDelimiters;

tabYourExcel:
LOAD *
Where Len(Team);
LOAD RecNo() as ID,
    TextBetween(Description,ApplyMap('mapDelimiters',IterNo())&'_',' ') as Team,
    ApplyMap('mapDelimiters',IterNo()) as Delimiter,
    *
Inline [
Description
1) Summary: Ticket Group: US_Team1 - on this date, xxx
2) Summary: Ticket Group: Asia_123Team - on this date, xxx
3) Summary: Ticket Group: Africa_234Team - on this date, xxx
4) Summary: Ticket Group: Americas_345Team - on this date, xxx
5) Summary: Ticket Group: Europe_456Team - on this date, xxx
6) Summary: Ticket Group: Australia_567Team - on this date, xxx
]
While IterNo()<=FieldValueCount('Delimiter');

hope this helps

regards

Marco

[Anonymous]

TextBetween seemed to work. How do I add multiple TextBetweens? I've tried OR and it didn't work.