Make Mine MAPPER #40 -------------------- by Rob Haeuser Well Slap Me Silly! Old Dogs Can Learn New Tricks! ----------------------------------------------------------------- Maybe it's because this is number 40. It must be a mid-life article crisis. Once again the brain has gone flat, like a dry- rotted tire that's been sitting in one spot so long it can stand up all by itself. A little sinkhole has formed on top of my head, and my wife has taken to using it for an ashtray. Now, whenever I bend over I get a Mt. Pinatubo simulation. Goggles help. Or maybe it was because I wore that tight leather hat in the rain. The sun sure dried it out awful fast. Guess I should've taken it off. Heck, my forehead's inch-deep crease still catches grease. But it sure is handy for keeping small objects and loose change. I just have to remember to keep looking up. But, enough of this. Even with a flat brain, you'd think a human being could at least equal an old dog in it's ability to learn new tricks. Unfortunately, there are no known documented cases of old dogs ever having learned anything beyond their 40th doggy year equivalent. What really got me started on this line of thought was a recent coding incident. I was writing a run that would take a large monthly transaction detail file and reduce groups of records into single records. It then stores the reduced data, reduces it again from a 10-digit key to an 8-digit, stores that, and then reduces again to a six-digit key. After all that it takes the three files and adds them to six others: the year-to-date and life- to-date files, for a total of nine files produced. Well, I don't even want to talk about the initial reduction routine. If I ever figure out a better way than Read/If logic to take a bunch of "vertical" records and "horizontalize" them by conditionally placing numbers in different fields depending on the value of sub-keys that also determine which mathematical formula to apply to that particular value, I'll let you know. In the meantime, I was feeling particularly proud of the fact that I had used the Count function for the subsequent reductions from a 10-digit key to 8 and then 6. Remarkably, I even figured that it would be more efficient to reduce the 8-digit-key report to six rather than reducing the 10-digit again, which was almost 50 percent larger than the 8-digit. So now I have these three monthly reduction files that need to be added to the year-and-life-to-date files. Here's where the old trick comes in. In the past, there were a very limited number of ways to combine two reports for further processing. You could search the two reports for some consistent value, such as a non-blank key, while using the "r" (report range) option to indicate which two reports to process. You would then need to sort and totalize (TOT) the search result. You could do a match with the b (blend) option, although you might have to pre-sort both reports. Or you could simply add the two together, sort them, and then TOT it up. I tended to lean toward the third option, figuring that this had the least impact on the machine (don't forget: save the nanos!). So, you probably have already guessed what I did with the monthly reports. Yep, I added the 10-digit monthly to the 10-digit yearly and counted it up. Then I added the 10-digit monthly to the 10-digit life-to-date and counted it up. Same thing for the 8-and- 6 digit stuff, too. Seemed innocent enough. It worked: what more do you want? What's that, you say? You want it to run in less than one hour? Sheesh! Well, alright, already! Quite frankly, there was hardly a place where I could shave anything off the process: these were just damn big files. But there was a little flea nibbling on my knee, and it made my leg jump, which hit my desk, rattling everything loose. My run design manual, teetering on the edge of the shelf, fell open in my lap on the count function options. Look at that! Count has a blasted report range (r) option, too. Hey, wait a minute! I've been old-tricking this mess with the Add function. Maybe I should just use the Count's range option to indicate which two reports to process. New trick. Duh. What a lark! I even had the two report numbers in variables already, so I simply put "RV22,V23" in the Count, dropped the Add, and shaved the execution time by 25% (which, by the way, did reduce execution time to under one hour)! Now I hate to sound like a whiner, but I still have to pass the entire Count result through the calculate function for one thing: the user hates seeing zero values. The Cal has a nice 'E' option that erases the field if the value is zeroes. The wonderful Count doesn't have a 'blank out zero values' option like Cal does. It would be nice for TOT, too. HINT, HINT, Unisys! So look out all you coding crazies. The subtleties can sometimes slam ya'. All you can do is try. If you've got some old tricks, periodically review 'em. And don't be surprised if the new dog on the block brings a few fleas with him. Finally, I'd like to bid a fond farewell to my editor for the last couple of years, Ron Stefani. He's moving on to bigger, better things... NOT! It's the bucks, boy! I gotta tell you, though. You wouldn't believe some of the weird stuff he's spared you from reading! So, for a complete, unedited set of the 40 Make Mine MAPPER articles, delivered to your door in a plain brown package, send $19.95 to the address listed below (I wonder if he'll cut this part?). Bye, Ron... ----------------------------------------------------------------- Rob Haeuser has more than 19 years of Data Processing experience. He was MAPPER Coordinator and Run Designer for the Texas Department of Human Services for ten years, and is now an independent contractor working in the Austin area. He also authored and is marketing a set of MAPPER run utilities called GURU. Covering MAPPER topics ranging from technical to tacky, his never-ending quest is for truth, justice, and the MAPPER way. Please direct all communications to: GURU Enterprises Attn Rob Haeuser 3212 Great Valley Drive Cedar Park, Tx. 78613 or call: 512-335-3862 (fax) / 512-331-0498 (voice)