Smart Matching

[etsmarines]

Here is what I am trying to do.

 

I have two tables.

 

the 1st table shows software names and a generic product name that I gave it myself.

 

Ex. 

 

SOFTWARE NAME                                           PRODUCT NAME I GAVE IT

CONTROL-M/Agent 6.4.01                                            Control-M Agent

Microsoft .NET Framework 2.0 Service Pack 2             .NET Framework

Microsoft .NET Framework 3.5 SP1                              .NET Framework

Microsoft Windows 2003 R2 SP2                                  Windows

 

 

Now the above is just a very small subset for an example so you know what i was doing basically.

 

Here is my new problem. Now that I have done this mapping which initially took a long time i would like to automate the mapping process (giving software names generic product names) in the future. I have imported a list of software names roughly 150000 names and would like to know if there is a way to do the following.

 

If Software Name contains somewhere in the name one of the product names give it that product name.

 

 

So for example.

 

If i imported Microsoft Windows 2008 R2 SP1 It would look through the product names list and see that The Product Name "Windows" exists and it is in the software name and give it that product name.

 

So software name Microsoft Windows 2008 R2 SP1 would automatically get Windows as a product name after a script was run or during the import.

 

Thanks for the help guys!

 

Erik

[comment]

If Software Name contains somewhere in the name one of the product names give it that product name.

 

What if you import a product named "A Wonderful Application for Windows"?

[etsmarines]

All the product names are ones that I gave and validated. So now i just want to give those names automatically to software names that contain the product name.

 

So in your case... A software name called a wonderful application for windows would automatically get the product name Windows. I just don't know how to do that smart match.

[Kris M]

I think it was comments intent to indicate the difficulty of your effort.. in his example the software name would be in fact be "Wonderful application for windows" and an your logic would identify it as being a product called windows when it is clearly NOT microsoft windows or any variant of that operating system.

[etsmarines]

That is fine. I know what i have in my list i just need to be able to write a script that does the following logic.

 

Loop

1) Copy Software name in first record.

2) Go though the product list until it finds a product name that is found anywhere in the software name string.

3) Copy that product name

4) Set the product name for record #1 to that copied product name that was matched

5) Go next record 

6) Repeat Loop.

 

Can anyone guide me in what i should do for this?

[Lee Smith]

Hi Erik,

 

I deleted your new thread of today because it was a duplicate of this thread. Please do not double post your questions. If you require more help on this, then just reply to this thread with the additional information.

 

If you have any questions of this actions, just send me a Private Message.

 

Lee

[etsmarines]

Can you give me any advice on the solution?

[Lee Smith]

Why not post a copy of the file?

 

If you need to know the steps, just follow this post Attach File

[eos]

That is fine. I know what i have in my list i just need to be able to write a script that does the following logic.

Can anyone guide me in what i should do for this?

 

You need two loops to examine the current software name word by word. Not tested:

Set Variable [ $productNameList ; … wherever you have this stored, or however you generate it ]
Go to Layout [ if necessary ]
Go to Record/Request [ first ]
# records loop
Loop
    Set Variable [ $curSWname ; YourTable::nameSoftware ]
    # sub-strings loop
    Set Variable [ $i ; 0 ]
    Loop
      # we've examined all sub-strings, or found a match; this assumes that we've started out with records with an empty YourTable::nameProduct field
      Exit Loop if [ Let ( $i = $i + 1 ; $i > WordCount ( $curSWname ) or not IsEmpty ( YourTable::nameProduct ) ) ]
      Set Field [ YourTable::nameProduct ; seeBelowForResultCalculation ]
    End Loop
  Go to Record/Request [ next; exit after last ]
End Loop

Here's the result calculation for Set Field []:

  Let ( [
    curSubString = MiddleWords ( $curSWname ; $i ; 1 ) ;
    pos = Position ( $productNameList ; curSubString ; 1 ; 1 ) ;
    index = ValueCount ( Left ( $productNameList ; pos ) )
    ] ;
    GetValue ( $productNameList ; index )
 )

Another approach might be using a calculated multi-line key as a match field against the table(?) where the product names are stored, and using Replace Field Contents (instead of a record loop) to get the product name of the first matching record as per that relationship's sort order.

 

EDIT: added loop reset, corrected syntax error (semicolon) in Let()

Edited May 6, 2014 by eos

[comment]

That is fine. I know what i have in my list i just need to be able to write a script that does the following logic.

 

Loop

1) Copy Software name in first record.

2) Go though the product list until it finds a product name that is found anywhere in the software name string.

3) Copy that product name

4) Set the product name for record #1 to that copied product name that was matched

5) Go next record 

6) Repeat Loop.

 

Can anyone guide me in what i should do for this?

 

Well, you have practically written the script yourself already, so I am not sure what further guidance you are looking for.

 

Except I don't think you should be dismissing the reservations about your logic so cavalierly. You need to consider that there could be more than one match, or there could be no match at all, or there could be one or more false-positive matches.  Even if you may not be concerned about these, remember that this is a public forum where ideas are thrashed out for the benefit of the entire community.

 

If I were facing this problem, I would look for a solution that would suggest possible matches for a human to select from. For example, one could split the software title into individual words and construct a relationship matching these words (as a multikey) against your index of keywords.

[etsmarines]

Yes. Thanks for the clarity. I understand the public forum thing and thanks for the helpful feedback. Can you give me an example of how a multi key works?

[comment]

A multi-key field contains multiple return-separated values. A relationship based on a multi-key field matches each value on its own. For example:

 

Records in table Genres, field Genre:

1 Rock

2 Pop

3 Classic

 

Records in table Media, fields Name, Genres:

1 Alpha, Pop

2 Bravo, Rock¶Pop

2 Charlie, Classic

 

 

With this relationship in place:

 

Genres::Genre = Media::Genres

 

the Bravo record will be related to both the first (Rock) and the second (Pop) record in the Genres table.

[etsmarines]

What would like script look like for replacing field contents based upon a closest possible match.

 

Example most of my software names contain many words and the product name can be found maybe at the beginning of the software name... maybe at the end or most times somewhere in the middle.

 

How do i create a script or calculation that searched through the name and pulls the closest match with one of the product names when it finds a match?

[eos]

What would like script look like for replacing field contents based upon a closest possible match.

 

You already received an example for a script that goes through your data and performs a check – which you asked for, but so far chose to ignore. 

 

Why not take this as a basis for some experimenting/research of your own?

[etsmarines]

i didn't ignore it. I tried it out. It only would perform the script on one record. Then to get the next record done i had to run the script again. Also it only looks at the left word in the entire software name not all of them. Therefore sometimes the software name actually has the product name embedded in it which would give a match but because only the left word is looked at it returns a possible match on the left word of the string and ignores all the rest.

[eos]

i didn't ignore it. I tried it out. It only would perform the script on one record. Then to get the next record done i had to run the script again

 

Well, there were two errors in the code (a syntax error in the calculation, and a missing loop counter reset) – but the outer loop (records) works just fine as described  … anyway, did I mention it was untested?

 

If you have a problem with a suggested approach or code, just ask; otherwise the poster may not even notice something's wrong.

 

See this sample file (don't know if the file name is accurate …)

SmartMatching_eos.fmp12.zip

[Lee Smith]

 

If you have a problem with a suggested approach or code, just ask; otherwise the poster may not even notice something's wrong.

 

 

 

Excellent Advice.

[etsmarines]

Thanks for the help guys! It seems to be working better. Im sorry to be such a nuisance but i am very new to the world of scripts and variables. I notice that software with the same keywords is getting different product names.

 

For example Microsoft SQL Server Native Client is getting the mapped product name of Integration Manager for Microsoft Dynamics GP. It should really be getting SQL Server. I think it is mapping to Microsoft in the product name but SQL Server is a product name in the list of products and really is a better match.

 

What should I do? Is there a way to list more than one result?

 

​Thanks again for all your help!