Compare multiple fields

Brian Element
Background

This script will take the same file from different points and time and compare multiple fields. Currently the compare funciton in IDEA only allows to compare on one field based on an amount field. This will allow for comparison of text and date fields also.

Documentation
Purpose of script: This script allows the user to compare the same file at two points in time or two files that are supposed to be the same. The script will first join the two files together using matching fields using the "Matches only" join function and then create a virtual field in the joined file to compare the fields that the user has selected. The comparison can be performed on character, numeric and date fields.  If the files are the same at different points in time the recno() could be used as a matching field.
When starting the script the user will see the following menu. The menu allows you to supply the primary file and secondary file (much in the same way as selected the primary and secondary file using a join). You can not join a file with itself.
The fields names in both files must both be the same and the types must also be the same. If the names or types are different the script will give an error.

 

The next option is to select the fields to match on by clicking on the Match button. See next page for the menu that opens up.

You may choose up to 8 fields to match on. Each field must be present in both files with the same type. All fields can be left blank except for the 1st match which must be selected. There must be at least one common field. If there are no common fields and the files are the same you can create a virtual field in both files using the @recno() function and join base on that field. This will only work if the files have the same number of transactions and are in the same order. The final menu allows you to select the fields to use in the comparison. 

The comparison can be done on character, numeric or date fields. The field name and type must be the same in each file. The default is to have all fields selected for comparison.

The final step is to choose a file name for the completed file.
The script will then join the two files together based on the join fields. It will then create a new field for each compare, the new field will be called COMPARE_ old field name.
The output would be the following:
· For a numeric field it would subtract one field from the other, so any changes would be reflected by having a non 0 as a result.
· For character fields it uses the @CompareNoCare() function, so any changes would be reflected by having a non 0 as a result.
· For date fields it uses @Age() function, so any changes would be reflected by having a non 0 as a result.
 
In order to find out which fields have changed you need to filter out all non 0 amounts in the new COMPARE_ fields.
A history is added that show the name of the primary and secondary file, the fields used to match and the fields used for comparison.
This script was developed by the GEL Group that is made up of Sunder Gee, Brian Element and Steve Luciani. If you have any comments or problems please drop me a note at elementb@rogers.com.

Tags

klement Sat, 09/26/2015 - 09:51

Hi Brain
I just tried the Compare Multiple iss. and got an error on line 638: - The specified fieldname is not contained within the database. Any clue please.
 

Brian Element Sun, 09/27/2015 - 08:58

Hi Klem, I am in the process of rewriting the script as there are some problems.  I will put it to the top of my list to finish and post it here when it is done.

Thanks

Brian

Bibin Sat, 01/23/2016 - 07:40

Hi Brian,
At current scenario, we are able to perform aging only for 6 buckets. Is there a way to create a macro that can be used to generate atleast 15 buckets

Bibin Sat, 01/23/2016 - 07:40

Hi Brian,
At current scenario, we are able to perform aging only for 6 buckets. Is there a way to create a macro that can be used to generate atleast 15 buckets

Brian Element Sun, 01/24/2016 - 06:26

HI Bibin,

Have you tried using the stratification for this?  It would allow you to create your 15 bands / buckets based on a date field.  If I created a macro for this what I would probably do is create an interface and then use the stratification to actually pull out the different buckets.

Try it out and let me know if it works for you.

Brian

aveenm293 Fri, 10/14/2016 - 08:20

Hi Brian
I saw this compare multiple fields script. Is there an ISS version? The one posted here is a txt file.
RegardsAveen

youness Wed, 11/23/2016 - 11:21

Hi Brian,
Did you degug this script since, if yes could you please share with as the latest version. Thanks

Brian Element Thu, 11/24/2016 - 06:12

Hi Youness, unfortunately there are still a few bugs with it.  I won't have any time to look at it to figure out the problems until this weekend.  So I will get back to you.