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.
Comments
klement
Sat, 09/26/2015 - 09:51
Permalink
Error
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
Permalink
Hi Klem, I am in the process
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
klement
Sun, 09/27/2015 - 12:32
Permalink
Thanks Brian Klem
Thanks Brian
Klem
Bibin
Sat, 01/23/2016 - 07:40
Permalink
Aging more than 6 buckets
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
Permalink
Aging more than 6 buckets
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
Permalink
HI Bibin,
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
Bibin
Tue, 02/16/2016 - 23:10
Permalink
Thanks, Brian,
Thanks, Brian,
It works....
Regards
Bibin
aveenm293
Fri, 10/14/2016 - 08:20
Permalink
Hi Brian
Hi Brian
I saw this compare multiple fields script. Is there an ISS version? The one posted here is a txt file.
RegardsAveen
Brian Element
Fri, 10/14/2016 - 09:43
Permalink
That is strange, if I do a
That is strange, if I do a right click on the link and a save as I can save it as an ISS file.
youness
Wed, 11/23/2016 - 11:21
Permalink
Hi Brian,
Hi Brian,
Did you degug this script since, if yes could you please share with as the latest version. Thanks
Brian Element
Wed, 11/23/2016 - 11:27
Permalink
Hi Youness and welcome to the
Hi Youness and welcome to the site.
I think I might have an updated version. I will check tonight.
Brian
youness
Thu, 11/24/2016 - 05:27
Permalink
Hi Brian,
Any update ?
Brian Element
Thu, 11/24/2016 - 06:12
Permalink
Hi Youness, unfortunately
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.
Brian Element
Sat, 11/26/2016 - 19:52
Permalink
Hi Youness, I just uploaded a
Hi Youness, I just uploaded a new version for you to try out.
Brian