Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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')
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;
Hi Massimo - thanks so much for code! Unfortunately it works for Alex but not Josh! Because Alex is first alphabetically?
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:
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;
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
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
maybe also helpful:
Keyword Mapping with Description
How load necessary information from unstructured column?
regards
Marco