Find Substring, and link to another field

[#1]

Hello,

I'm sorry if this is confusing, but it's a bit difficult to phrase. I am looking to search within a field, and if a match is found, return another field from the substring. Below are a fewexamples. The values I'm searching in are not standardized, meaning not the same format with underscores or something similar.

DataSet:

Territory	Coast???
Sales_NorthEast_123	from MatchTable
Manager_xyz_West_012	from MatchTable
Manager_Growth_Northeast_123	from MatchTable

MatchTable:

if Territory contains...	then Coast is...
Northeast	Atlantic
West	Pacific

Final Dataset (Coast is the field I'm looking for)

Territory	Coast
Sales_NorthEast_123	Atlantic
Manager_xyz_West_012	Pacific
Manager_Growth_Northeast_123	Atlantic

Does this make sense? Thanks for any help!

EDIT - I should have mentioned that there are many records to match from, and I don't want to hardcode the cross-ref as this dashboard will be used for many years and would like it to be able to handle new organizations.

Thanks for your feedback thus far. Much appreciated.

[sunny_talwar]

Try this:

Mapping:

Mapping

LOAD Upper([if Territory contains...]) as [if Territory contains...],

     [then Coast is...] as [then Coast is...]

FROM

[https://community.qlik.com/thread/165611]

(html, codepage is 1252, embedded labels, table is @2);

Table:

LOAD Territory,

  ApplyMap('Mapping', Upper(SubField(Territory, '_', -2))) as Coast

FROM

[https://community.qlik.com/thread/165611]

(html, codepage is 1252, embedded labels, table is @1);

[sunny_talwar]

PFA a sample application

[#4]

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:

DataSet:

LOAD distinct

     Territory

From Dataset;

Join

Load

     Match_Territory,

     Coast

From MatchTable;

Final:

LOAD

     Territory ,

     Coast

Resident DataSet

Where Substringcount( Upper(Territory) , Upper(Match_Territory))

;

[swuehl · Accepted solution]

Maybe like this:

MatchTable:

LOAD * INLINE [

F1,F2

Northeast, Atlantic

West, Pacific

];

Map1:

MAPPING LOAD F1, '<'&F2&'>' as F2 Resident MatchTable;

Map2:

Mapping LOAD Upper(F1) as F1, '<'&F2&'>' as F2 Resident MatchTable;

DataSet:

LOAD Territory,

  Textbetween(MapSubString('Map1',Territory),'<','>') as Result,

  Textbetween(MapSubString('Map2',UPPER(Territory)),'<','>') as ResultCaseInsensitive

INLINE [

Territory

Sales_NorthEast_123

Manager_xyz_West_012

Manager_Growth_Northeast_123

];

Showing a case sensitive or insensitve match. You can replace '<' and '>' with any characters that are not part of your territory text values.

[stigchel]

I had almost exactly the same, apart from the case insensitive bit ... So not sure if this will still help, but my example attached

[#7]

That's beautiful! Need to read up a bit on the MapSubString, but it works like a charm.

Thank you.