Applymap + substring in QlikSense

[moshé]

Hello Guys,

I'm trying to do something similar to a "mapping" with "substring". my goal is to identify if in a source table one of the fields contains a substring and, if so, map it with another field from another table.

I leave a small example after the source data, the desired result and what I have tried to do so far:

1/ data table

2/ map table

3/ expected result

4/ attempt made

concepts:
mapping LOAD 
    "Whole Concept",
    "Simplified Concept"
FROM [lib://escritorio/concepts.xlsx]
(ooxml, embedded labels, table is Hoja1);


expenses:
LOAD
	MapSubString('concepts', "Whole Concept") as Simplified,
    "Whole Concept",
    Amount
FROM [lib://escritorio/expenses.xlsx]
(ooxml, embedded labels, table is Hoja1);

[JGMDataAnalysis]

Concepts:
MAPPING LOAD
    Lower("Whole Concept"),
    '<' & "Simplified Concept" & '>'

FROM [lib://escritorio/concepts.xlsx]
(ooxml, embedded labels, table is Hoja1);

Expenses:
NOCONCATENATE
LOAD
    TextBetween(
        MapSubString('Concepts', Lower("Whole Concept")),
            '<', '>') AS Simplified,
    "Whole Concept",
    Amount

FROM [lib://escritorio/expenses.xlsx]
(ooxml, embedded labels, table is Hoja1);

[Vegar]

You can do this by using a pick wildmatch approach.

Load

[Whole Concept], 

Pick(Wildmatch([Whole Concept], '*restaurant*','*supermarket*'), 'Restaurant','Supermarket') as [Simplified Concept],

Amount

From Data; 

Check out @rwunderlich blog on the topic https://qlikviewcookbook.com/2008/10/the-match-function/