OMG thinking…also known as Outside the Box thinking… also know as “Aha” thinking…
Kevin Frank has a great post (its the third in the series) on anti-deduping (retain duplicates and omit unique entries from within an existing found set, as opposed to starting from all records). Why would you want to do that? This is from his first post on the subject:
For a given found set of customers, how can I omit those whose Zip codes only appear once in the found set?
In other words, keep the records whose Zips appear multiple times and banish the others.
Alright, that is a bit more clear. Skip now to the latest post:
- Today we have two great examples of using demos from this site as a starting point and making significant improvements: one from Malcolm Fitzgerald in response toFM 13: Anti-deduping, part 1, and one from Ralph Learmont in response to FM 13: Anti-deduping, part 2.
- As you may recall the challenge was to retain duplicates and omit unique entries from within an existing found set, as opposed to starting from all records… otherwise we could have just searched on ! (find all duplicates), but since the ! operator does not play nicely with constrain, it was apparently not an option for this particular challenge.
- Or so I thought. These gentlemen convincingly prove otherwise — and like all great techniques, what you’re about to see has the potential to be useful in a variety of situations, not just the narrow confines of this particular challenge.
The OMG thinking has to do with temporarily renaming a window, then doing a constrained search. Why this works, nobody seems to know. And how Ralph Learmont (it was his idea) thought of this, I’ll never know.
Check it out, and one note: There are three downloadable example files at the link.