Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
sinanozdemir
Specialist III
Specialist III

There you go:

C.PNG

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:

C.PNG

C.PNG

I am also attaching my qvw.

Hope this helps.

View solution in original post

15 Replies
sinanozdemir
Specialist III
Specialist III

Hi Jennifer,

Here is my approach:

Capture.PNG

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

Capture.PNG

Hope this helps.

malini_qlikview
Creator II
Creator II

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

Anonymous
Not applicable
Author

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?

sinanozdemir
Specialist III
Specialist III

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.

Anonymous
Not applicable
Author


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

CA

MA

MI

TX

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

sinanozdemir
Specialist III
Specialist III

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

Thanks

Anonymous
Not applicable
Author

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.

sinanozdemir
Specialist III
Specialist III

There you go:

C.PNG

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:

C.PNG

C.PNG

I am also attaching my qvw.

Hope this helps.

MarcoWedel

Hi,

another solution could be:

QlikCommunity_Thread_185644_Pic1.JPG

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