How can we compare two dynamic field values from two lookups

[Trishla]

Hi All,

I am urgently looking for a help . I have one field object_name which is present in lookup X1.csv and has values like 
object_name
GRM MGT Shortfirer Appointment 
Blasting Security Register Test
Morning Schedule

The other lookup(X2.csv)  has the column object_name , which has values like below
Appointment
Schedule
Blasting
I have to match the two columns and give the results , wherever object_name contains *keyword* of object_name from secondlookup.. The field values can be in upper case or lower case or a combination.

[bowesmana]

It's not clear what exactly you are looking up from each lookup file. So when you have a row with

object_name="Blasting Security Register Test"

then are you wanting to see if any of the 4 words

• Blasting
• Security
• Register
• Test

exist in the second lookup file?

What you can do is based on the following example

This will create a lookup file with the 4 words separated onto individual rows with a value n=1 for each

| makeresults
| eval object_name=split("Blasting Security Register Test"," ")
| mvexpand object_name
| table object_name
| eval n=1
| outputlookup mylookup.csv

This second snippet with then create a test sample where there are two rows, one with the name as above and the other with a name where the fields are not present.

| makeresults count=2
| eval t=1 
| accum t
| eval object_name=if(t=1,split("Blasting Security Register Test"," "),split("GRM MGT Shortfirer Appointment "," "))
| fields - t
| lookup mylookup.csv object_name

Essentially what this is doing is to split the words from the object_name and then do a multivalue lookup of those words into the lookup file. 

By then testing the existence of n, you will know if the match is found

| where !isnull(n)

You will need to create not just the lookup file, but also the lookup definition where you say that lookups are case insensitive, so you do the lookup on the definition NOT the lookup file itself.

Hope this helps