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.

[sinanozdemir]

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.

[malini_qlikview]

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]

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]

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]

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]

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

Thanks

[Anonymous]

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 · Accepted solution]

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.

[MarcoWedel]

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