Multi-wildmatch on Text

[#1]

Hello!

Imagine you have the following four emails:

1. 'Hello, my name is Alex'

2. 'Hello, Alex, my name is Josh'

3. 'Nice to meet you Josh'

4. 'Let's fire Barry he's an idiot'.

In the dataset, all the above is contained in a table under the column 'E_EmailText'

I want to write something in my script which identifies emails about who the email is about, i.e. does it contain Josh, Alex, or both?

Now, this almost works:

if(wildmatch(E_EmailText,'*Alex*'),'Alex',

if(wildmatch(E_EmailText,'*Josh*'),'Josh','Other')) as E_WhoTheEmailisAbout

BUT, In email 2 it will be listed as 'Alex' (as it comes first), when in fact it should be a many to many relationship right?

How do I make it understand that a text string could include more than one of the possible categories?

Thank you!

[malini_qlikview]

Hi,

Can you please try this

=If(Index('Hello, Alex, my name is Josh','Alex')>0,'Alex ')&

If(Index('Hello, Alex, my name is Josh','Josh')>0,' Josh')

[maxgro]

source:

load *, rowno() as id inline [

E_EmailText

1. 'Hello, my name is Alex'

2. 'Hello, Alex, my name is Josh'

3. 'Nice to meet you Josh'

4. 'Let's fire Barry he's an idiot'.

] (delimiter is '\t');

tmp:

load

'|' &

if(wildmatch(E_EmailText,'*Alex*'),'|Alex',

if(wildmatch(E_EmailText,'*Josh*'),'|Josh',

if(wildmatch(E_EmailText,'*Barry*'),'|Barry',

'Other'))) as e,

id

Resident source;

link:

load *

Where len(trim(E_WhoTheEmailisAbout))>0;

load

  id, SubField(e, '|') as E_WhoTheEmailisAbout

Resident tmp;

DROP Table tmp;

[#4]

Hi Massimo - thanks so much for code! Unfortunately it works for Alex but not Josh! Because Alex is first alphabetically?

[petter]

I think MapSubstring used with a mapping table could be a good solution for you. Have a look at this and the included example QVW which these screenshots are from:

[maxgro]

changed the bold

source:

load *, rowno() as id inline [

E_EmailText

1. 'Hello, my name is Alex'

2. 'Hello, Alex, my name is Josh'

3. 'Nice to meet you Josh'

4. 'Let's fire Barry he's an idiot'.

5. Alex, Josh, Massimo

] (delimiter is '\t');

tmp:

load

'|' &

if(wildmatch(E_EmailText,'*Alex*'),'|Alex') &

if(wildmatch(E_EmailText,'*Josh*'),'|Josh') &

if(wildmatch(E_EmailText,'*Barry*'),'|Barry')

as e,

id

Resident source;

link:

load *

Where len(trim(E_WhoTheEmailisAbout))>0;

load

  id, SubField(e, '|') as E_WhoTheEmailisAbout

Resident tmp;

DROP Table tmp;

[rwunderlich]

This example is similar to Petter's, but slightly different script:

Qlikview Cookbook: Indexing Keywords in Text http://qlikviewcookbook.com/recipes/download-info/indexing-keywords-in-text/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[MarcoWedel]

Hi,

one solution could be also:

tabPersons:
LOAD * INLINE [
    Name, Address, Phone
    Alex, somewhere, 555-SHOE
    Barry, anywhere, 555-1234
    Josh, elsewhere, 555-5678
];

tabEmails:
LOAD RecNo() as E_EmailID, *
INLINE [
    E_EmailText
    "Hello, my name is Alex"
    "Hello, Alex, my name is Josh"
    Nice to meet you Josh
    "Let's fire Barry he's an idiot"
];

tabLink:
LOAD E_EmailID,
    KeepChar(SubField(E_EmailText,' '),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') as Name
Resident tabEmails;

Right Join (tabLink)
LOAD Name Resident tabPersons;

hope this helps

regards

Marco

[MarcoWedel]

maybe also helpful:

Keyword Mapping with Description

How load necessary information from unstructured column?

regards

Marco