Search code in variable length Text string

[rob_vander]

Hi All,

I have below data which stores the specific code with their Category

Code	Category
A->B	ABC
A!B	XYZ
A|B	PQR
A+B	DEF

I need to search each code in actual data which contains Text string of variable lenght and map the corresponding category. I have actual data as follows

ID	Text	Date
1	123-A->B456	01/01/2024
1	A|B45626abc	14/01/2024
2	abc1234A+B	24/01/2024
3	abc4123A!B1234	01/01/2024
4	A|B123abcA+B	25/01/2024

I need below output where I will have to add category column.

ID	Text	Date	Category
1	123-A->B456	01/01/2024	ABC
1	A|B45626abc	14/01/2024	PQR
2	abc1234A+B	24/01/2024	DEF
3	abc4123A!B1234	01/01/2024	XYZ
4	A|B123abcA+B	25/01/2024	PQR

Another challenge is that, Text can have multiple codes. In this case, we need to pick up first match in the Text. See the example for ID=4, it has two codes but we need to pick up first match which is A|B and map the corresponding Category

[Or]

Wildmatch(Text, '*A->B*','*A!B*','etc') will return 1, 2, etc for the first match found.

If you need the list to be pulled from a table, have a look at e.g. https://community.qlik.com/t5/App-Development/Wildmatch-with-a-list/td-p/1731085 or perhaps https://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

[rob_vander]

@Or  Thanks for the reply. My ultimate goal is to add Category column in main table. I don't know how I can add that using wildmatch.  Code and Category stored in different table. I need to search that Code in Text column of main table. Also, there will be many Codes which are getting updated once in quarter, hence I don't want to hardcode the search values. Is there any other way?

[rob_vander]

@Or  I tried with below approach but it doesn't work as expected. It's giving NULL value for ID=2 because A+B is present in both ID=2 & ID=4. Also, for ID=4 

pick(WildMatch(Text,'*A->B*','*A!B*','*A|B*','A+B'),'ABC','XYZ','PQR','DEF') as Category

[Kushal_Chawda · Accepted solution]

@rob_vander  If you don't want to hardcode, try below

// Use the characters which are not part of Text string
Codes:
mapping Load Code,
            '@' & Category & '*' as Text
Inline [
Code	Category
A->B	ABC
A!B	XYZ
A|B	PQR
A+B	DEF
](delimiter is '\t');

Data:
Load *, 
     TextBetween(MapSubString('Codes',Text),'@','*') as Category
Inline [
ID	Text	Date
1	123-A->B456	01/01/2024
1	A|B45626abc	14/01/2024
2	abc1234A+B	24/01/2024
3	abc4123A!B1234	01/01/2024
4	A|B123abcA+B	25/01/2024
](delimiter is '\t');

[thiago_mlg]

Para adicionar uma coluna na sua tabela, basta incluir o código abaixo na hora de carregar a sua tabela.

IF(WildMatch(Text,'*A->B*','*Um|B*','*Um + B*','*Um!B*') = 1 , 'Abc',
  IF(WildMatch(Text,'*A->B*','*Um|B*','*Um + B*','*Um!B*') = 2, 'PQR',
    IF(WildMatch(Text,'*A->B*','*Um|B*','*Um + B*','*Um!B*') = 3, 'DEF',
      IF(WildMatch(Text,'*A->B*','*Um|B*','*Um + B*','*Um!B*') = 4, 'XYZ')))) as CATEGORY,

Att,

Thiago Gonçalo

[rob_vander]

@thiago_mlg I adjusted your if as below and it works but thing is I don't want to hardcode codes in If because I have around 20 codes. But thanks for the reply.

IF(WildMatch(Text,'*A->B*') = 1 , 'ABC',
IF(WildMatch(Text,'*A|B*') = 1, 'PQR',
IF(WildMatch(Text,'*A+B*') = 1, 'DEF',
IF(WildMatch(Text,'*A!B*') = 1, 'XYZ')))) as CATEGORY

[rob_vander]

Thanks. I will try it on my data

[rob_vander]

Thanks. It works