Lookup, Wildmatch

[Anonymous]

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

CA

MA

MI

TX

and in the Short Description we have something like:

CA account updated

issue was reported in MA

abcdef TX1234

Please help. Thank you.

[qlikviewwizard]

Hi,

Please check this thread Apply Map Functions - Sources

[evan_kurowski]

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

[rwunderlich]

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

[Anonymous]

Thank you, this worked!

[sinanozdemir]

You are welcome.

[evan_kurowski]

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.