If you’re an ACL user, I sure hope you read your ACL project logs and approach the JOIN command carefully. I recently received a good reminder. For an explanation of ACL, see this post.
I was sipping a nice, hot coffee and joining 2 tables of data that were (supposedly) identical except that:
- Table A had a field called Host that wasn’t in Table B.
- Table A had 10,000 records from production systems and was the primary table (before loading the file into ACL, I had filtered out the test system data by selecting only production systems in the Host field).
- Table B had 25,000 records, including some from test systems that I didn’t care about, so it was the secondary table.
- Both tables had a field called Cancel Date.
I needed to confirm that both lists contained the same 10,000 production system records and the same values in the Cancel Date field.
Setting up the Join
I joined the tables using 7 primary and secondary keys and requested only matched primary records (consisting of the 7 primary key fields plus the Cancel Date from the secondary field) be in the joined table. The resulting table contained 10,000 records, so everything looked good to go. Some of the Cancel Date fields were blank, but that was also true of the secondary table.
However, when I reviewed the project log, I noted the message:
10,000 records processed
3 unmatched records overwritten with nulls
What? 3 unmatched records? I did a quick ISBLANK command on one of the first fields in the joined table and found that no records had nulls in every field. What did this message mean? I glanced down at my coffee cup and found that it, too, was full of nulls, so I went for another cup to give me more time to think.
Tweaking the Join Command
When I came back, I reran the same join command (see this post for how to do this quickly without a lot of work), but changed the option on the MORE tab to Unmatched primary records. Sure enough, 3 records appeared, but they were not blank records. Only the Cancel Date field was blank. I checked the secondary table and noted that the Cancel Date was NOT blank for those 3 records, but contained a different date than the same records in the primary table.
After showing my esteemed colleague everything I’d found, he said, “Although the message says the 3 records were overwritten with nulls, it means the field that didn’t match was overwritten with nulls, not the entire record. The message is unclear, but that’s what it means.”
So heads up, everyone. Read your logs and make sure when you match tables you run it using matched records and then unmatched records, and make sure both sets make sense.
Moral of the story:
One missed message can make 3 of 10,000 records worthless.
Check out our other posts about ACL:
Teach Yourself ACL (most popular post on this blog)