ACL: Strange Message in Log

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.

Log Troubles

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)

ACL tip: Rerun a Join Easily

ACL tip: “Unfreezing ACL”

All ACL-related posts

Leave a Comment

Leave a comment

Filed under ACL, Data Analytics, Written by Skyyler

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s