topic Re: Multi-wildmatch on Text in QlikView

Multi-wildmatch on Text

Fri, 04 Sep 2015 15:26:06 GMT

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!

Re: Multi-wildmatch on Text

malini_qlikview — Fri, 04 Sep 2015 15:41:28 GMT

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

Re: Multi-wildmatch on Text

maxgro — Fri, 04 Sep 2015 15:48:28 GMT

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,

Resident source;

link:

load *

Where len(trim(E_WhoTheEmailisAbout))>0;

load

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

Resident tmp;

DROP Table tmp;

Re: Multi-wildmatch on Text

Fri, 04 Sep 2015 16:01:46 GMT

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

Re: Multi-wildmatch on Text

petter — Fri, 04 Sep 2015 16:56:44 GMT

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:

Re: Multi-wildmatch on Text

maxgro — Fri, 04 Sep 2015 16:58:25 GMT

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,

Resident source;

link:

load *

Where len(trim(E_WhoTheEmailisAbout))>0;

load

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

Resident tmp;

DROP Table tmp;

Re: Multi-wildmatch on Text

rwunderlich — Fri, 04 Sep 2015 19:04:59 GMT

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

Re: Multi-wildmatch on Text

MarcoWedel — Fri, 04 Sep 2015 22:35:42 GMT

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

Re: Multi-wildmatch on Text

MarcoWedel — Fri, 04 Sep 2015 22:49:23 GMT

maybe also helpful:

Keyword Mapping with Description

How load necessary information from unstructured column?

regards

Marco