LOAD Upper([if Territory contains...]) as [if Territory contains...],
[then Coast is...] as [then Coast is...]
(html, codepage is 1252, embedded labels, table is @2);
ApplyMap('Mapping', Upper(SubField(Territory, '_', -2))) as Coast
(html, codepage is 1252, embedded labels, table is @1);
Hello, If your Match table is too small and table is static, you can use Wildmatch in IF cond like below.
IF(WildMatch(Territory,'*northeast* , '*west*'), Pick( WildMatch(Territory,'*northeast* , '*west*') ,'Atlantic','Pacific') , 'NA' )
If your Match table is too much the values are changing with short period, then do the cross join & search against each values like below:
Where Substringcount( Upper(Territory) , Upper(Match_Territory))
Maybe like this:
LOAD * INLINE [
MAPPING LOAD F1, '<'&F2&'>' as F2 Resident MatchTable;
Mapping LOAD Upper(F1) as F1, '<'&F2&'>' as F2 Resident MatchTable;
Textbetween(MapSubString('Map1',Territory),'<','>') as Result,
Textbetween(MapSubString('Map2',UPPER(Territory)),'<','>') as ResultCaseInsensitive
Showing a case sensitive or insensitve match. You can replace '<' and '>' with any characters that are not part of your territory text values.