Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Applymap + substring in QlikSense

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

image1.png

2/ map table

image2.png

3/ expected result

image3.png

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);

 

 

Labels (6)
2 Replies
Highlighted
Creator II
Creator II

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);
Highlighted
Partner
Partner

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/

Plees ekskuse my Swenglish and or Norweglish spelling misstakes