File Comparison

What the best to compare two files?

Each file has two fields

There is no general answer to your question. For anyone to provide useful assistance, you’ll have to provide more information about what is in the files and what you mean by “comparing”.

Each file might not contain all of the entries.
File Number #1

Title of Film
Year

File Number #2

Title of Film
Year

Entry of film title might not match exactly in each file, because it was entered by different people. Partial match on title and year is the same.

The goal is to end up with a list in file #1 that are not in file #2 (unique items in file #1)
The goal is to end up with a list in file #2 that are not in file #1 (unique items in file #2)

You can use select with a lookup. This could be done with the Find/Select dialog with the formula option, but I think it would be easier to do with a short procedure. In File #1, you would use this procedure.

select lookup("File Number #2","Title of Film",«Title of Film»,"Title of Film","")=""

The procedure in File #2 would be the same except you would change the first parameter of the lookup to “File Number #1”.

Jim,

That’s look promising, thanks!!!

Will this search give me contains results or look for exact match
Also when the title is not exactly matched, I have a year field that will insure the match.

Will the code below work

select lookup(“File Number #2”,“Title of Film”,«Title of Film»,“Title of Film”,"")=""
lookupselected(“File Number #2),“Year”,«Year»,“Year”,”")=""

The first statement find all the potential matches
The second should insure the correct match by matching the year

I get unknown statement on lookupselected

The lookup( function is for exact matches only. If you want a contains match, you can use the superlookup( function. It’s a bit more complex to use, but also way more flexible.

No, this will not work. lookupselected( is a function, not a statement.

I don’t understand what you mean by this. I would think that the year would be useless for matches, since there would be many movies made in a particular year.

How do step through the records of file #1?

I’m so use in going through For Next Loop through the records.
Is this necessary in Panorama?

File #1 has 3132 Records Loop through the set.
I’ve only used single files in Panorama before.

My execution of the following code gives me one match

noshow

field “Name”
field “Year”
field “Found”

select lookup(“File #2”,“Name”,«Name»,“Name”,"")
Found=“F”
endnoshow showpage

The select statement will scan all of the records in the database, no need for a loop in your code (and much faster than a loop in your code). The end result will be that the records that have a match will be selected.

It sounds like you want to fill the Found field with F when there is a match. Here is how you can do that.

select lookup("File #2","Name",Name,"Name","")<>""
field Found
formulafill "F"

Or, you could do this:

field Found
formulafill ?(lookup("File #2","Name",Name,"Name","")<>"","F","")

Jim, Thank You!

This was super helpful.

Here’s my code

I created a new field that merged Year and Title together.
I submitted this field in the Lookup
I then use morph command to do the following

Morph All Capitalization
Eliminate spaces
Eliminate Strip Tags ( )
Eliminate hyphens
Eliminate colons

Using just title I found 2096 matches out of 3132 record
Adding the year in the mix and eliminating all unusual characters
The matches raised to 2760

Thanks again for all of your help.
You saved the day.

Best,
Chuck