topic Re: Lookup, Wildmatch in QlikView

Lookup, Wildmatch

Anonymous — Tue, 06 Oct 2015 13:34:28 GMT

Hi I have a Short Description column in one file and I have another column called Abbreviations in another file.

I need to see if there are any matches in the Short Description column using the Abbreviations column.

For example, in the Abbreviations we have

and in the Short Description we have something like:

CA account updated

issue was reported in MA

abcdef TX1234

Please help. Thank you.

Re: Lookup, Wildmatch

sinanozdemir — Tue, 06 Oct 2015 14:44:28 GMT

Hi Jennifer,

Here is my approach:

First, with KeepChar(), I only picked the abbreviations from the short descriptions and then used ApplyMap().

Hope this helps.

Re: Lookup, Wildmatch

malini_qlikview — Tue, 06 Oct 2015 15:10:16 GMT

try this in front end

1. declare a variable ='*' & Concat(Abbreviations,'*'&Chr(39)&',' &Chr(39)&'*')&'*'

2. use wildmatch to check if the abbreviation matched with the description =Wildmatch(Desc,'$(variable )')

Re: Lookup, Wildmatch

Anonymous — Tue, 06 Oct 2015 15:29:35 GMT

This is going to be a lot of work if I have to type out all the values in that Abbreviations column. I used the states as an example but this column has 300 values or so. Can it just go through the rows 1 by 1 in the file?

Re: Lookup, Wildmatch

sinanozdemir — Tue, 06 Oct 2015 20:48:11 GMT

Are all these fields in just one table or file? If so or not, you can just extract the abbreviations and create a column that contains the value of "Match":

Mapping LOAD

Field As Abbreviations,

'Match' As Comp

Resident File_Or_Table_Name;

You don't need to type it 300 times.

Hope this helps.

Re: Lookup, Wildmatch

Anonymous — Wed, 07 Oct 2015 11:53:25 GMT

The typing I was referring to was for values in the Abbreviations column:

I only used this as an example, but I have 300 records that I need to find matches for.

Re: Lookup, Wildmatch

sinanozdemir — Wed, 07 Oct 2015 12:39:53 GMT

Can you post a sample data-set? It seems like your problem is a bit different than what you originally posted.

Thanks

Re: Lookup, Wildmatch

Anonymous — Wed, 07 Oct 2015 19:30:46 GMT

Say my Abbreviations column in file A contains 300 + rows of data, example:

Abbreviations

Common

Change

Sales

Sametime

Senior

Shop

and my Short Description column in file B contains x amount of rows of data example:

Short description

Common Access Issue

Change Issue

Change delay

Unable to access in Sales

Sametime: Showing wrong contact information

Sametime contact information is incorrect

70 - Shop - Wrong user

What I'd like to do is have my script search all the keywords from the Abbreviations column into the Short Description column (without having to type or list out every single value from this spreadsheet). If there is a match then, I need it to extract that Abbreviation. So in this example above, Common Access Issue from Short Description column would return 'Common' and Change issue and Change delay would both return 'Change' from abbreviations.

Re: Lookup, Wildmatch

sinanozdemir — Thu, 08 Oct 2015 16:46:16 GMT

There you go:

In the mapping load (Abbreviations table), I pulled the Abbreviations column and also created another column by adding '*' so that I can extract the word by using TextBetween() function in the Short Descriptions table.

In the short description table, I first used MapSubstring() to pull the matching word from Comp column of the mapping table and in the second preceding load, I used TextBetween() to extract the matching words.

Here is the data model and a table:

I am also attaching my qvw.

Hope this helps.

Re: Lookup, Wildmatch

MarcoWedel — Fri, 09 Oct 2015 23:41:54 GMT

Hi,

another solution could be:

tabAbbr:
LOAD * Inline [
Abbreviations
Common
Change
Sales
Sametime
Senior
Shop
];

tabShortDesc:
LOAD * Inline [
Short description
Common Access Issue
Change Issue
Change delay
Unable to access in Sales
Sametime: Showing wrong contact information
Sametime contact information is incorrect
70 - Shop - Wrong user
];

tabLink:
LOAD *
Where Exists(Abbreviations);
LOAD [Short description],
    SubField([Short description],' ') as Abbreviations
Resident tabShortDesc;

hope this helps

regards

Marco

Re: Lookup, Wildmatch

qlikviewwizard — Sat, 10 Oct 2015 03:55:05 GMT

Hi,

Please check this thread Apply Map Functions - Sources

Re: Lookup, Wildmatch

evan_kurowski — Sat, 10 Oct 2015 04:57:36 GMT

I think this use case might have similarities to the "Key Word associator". You might want to peruse this thread for incorporating ideas...

Key Words_MOD_v2.qvw

//Thought key phrases
[Emotions]:
LOAD * Inline [
Perception_Key, Thought
1, "frustrating, frustrated, frustration, frustrate"
2, "efficient, good job, great work"
3, "friendly, happy, pleased"
4, "rude, not pleasant"
];

[Thoughts]:
NOCONCATENATE LOAD DISTINCT
Perception_Key,
Trim(Subfield(Thought,',')) AS Thought
RESIDENT Emotions;

DROP TABLE [Emotions];

//Perception key phrases
[Perception_Groups]:
Load * Inline [
Perception_Key, Perceptions
1, "CE negative, Staff poor"
2, "CE effective"
3, "CE positive"
4, "Staff poor"
];

[Perceptions]:
NOCONCATENATE LOAD DISTINCT
Perception_Key,
Trim(Subfield(Perceptions,',')) AS Perceptions
RESIDENT [Perception_Groups];

DROP TABLE [Perception_Groups];

// Full Comments
[Full_Comments]:
Load * Inline [
ID|Comments
1 |I felt really frustrated about my service, but you guys are really friendly. Im ambivalent
2 |My experience was not pleasant and a frustrating experience. Im going to switch to friendlier service.
3 |The install was efficient however the staff were rude and not pleasant.
4 |That was some great work there, you did a really good job.
5 | You are friendly, that makes me happy, ergo I am clearly pleased.
](delimiter is '|');

For i = 0 to NoOfRows('Thoughts')-1

Let vPEEK_Thought = Peek('Thought',$(i),'Thoughts');

[Comment_Thoughts]:
LOAD ID,
'$(vPEEK_Thought)' AS Thought
RESIDENT Full_Comments
WHERE Index(Comments,'$(vPEEK_Thought)')>0;

NEXT

Re: Lookup, Wildmatch

rwunderlich — Sat, 10 Oct 2015 11:53:24 GMT

And yet another similar but slightly different approach:

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Lookup, Wildmatch

Anonymous — Thu, 15 Oct 2015 18:51:13 GMT

Thank you, this worked!

Re: Lookup, Wildmatch

sinanozdemir — Thu, 15 Oct 2015 18:53:27 GMT

You are welcome.

Re: Lookup, Wildmatch

evan_kurowski — Fri, 16 Oct 2015 03:43:35 GMT

Lots of great suggestions here...

If I could offer one more, this is a kind of inverse approach.

In the custom API guide, there's a script of tab called 'Extract Subfields' that parses full VB statements in order to extract all variable names, commands, and regular or comment words, and associates them to their full code examples.

Typing a term into a search object would have the same effect, but this allows making a list of the potential searchable terms, whether into a list box or other object so the search terms can be perused, reported on frequency, see side by side variations, etc..

But basically what I'm expecting this to do is assemble the keyword list from the larger descriptions. For example, this might allow examination of wc (word count). Show frequency on the field [Subfields] allows quick identification/access to the most frequently appearing search terms found in the description fields.