Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jmarotte
Contributor III
Contributor III

Lookup, Wildmatch

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.

15 Replies
qlikviewwizard
Master II
Master II

Hi,

Please check this thread Apply Map Functions - Sources

evan_kurowski
Specialist
Specialist

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

jmarotte
Contributor III
Contributor III
Author

Thank you, this worked!

sinanozdemir
Specialist III
Specialist III

You are welcome.

evan_kurowski
Specialist
Specialist

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.