Wednesday, June 5, 2013

Closer to Madness

In addition to being a really good song by the awesome guitar player Jesse Cook, it's also rapidly getting to be my state of mind.

I started with this.

SELECT MASTERFAC_WKSPC_MAIN_TABLE_LOC.CLEAN_UWI, MASTERFAC_WKSPC_MAIN_TABLE_LOC.RECORD_SOURCE, MASTERFAC_WKSPC_MAIN_TABLE_LOC.SOURCE_STATUS, MASTERFAC_WKSPC_MAIN_TABLE_LOC.NUM, MASTERFAC_WKSPC_MAIN_TABLE_LOC.ID, MASTERFAC_WKSPC_MAIN_TABLE_LOC.NUMERIC_ID, MASTERFAC_WKSPC_MAIN_TABLE_LOC.EXTRA_VAL_10, MASTERFAC_WKSPC_MAIN_TABLE_LOC.EXTRA_VAL_6, MASTERFAC_WKSPC_MAIN_TABLE_LOC.STATUS_1, [PV Service Status Codes].PV_Status_Desc, MASTERFAC_WKSPC_MAIN_TABLE_LOC.STATUS_2
FROM MASTERFAC_WKSPC_MAIN_TABLE_LOC LEFT JOIN [PV Service Status Codes] ON MASTERFAC_WKSPC_MAIN_TABLE_LOC.STATUS_1 = [PV Service Status Codes].PV_SERVICE_STATUS
WHERE (((MASTERFAC_WKSPC_MAIN_TABLE_LOC.CLEAN_UWI) Is Not Null) AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.RECORD_SOURCE)="ROUGHNECK") AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.SOURCE_STATUS) Not Like "MISSING") AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.TYPE_1)="Tank") AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.NUMERIC_ID) Is Null) AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.STATUS_1) Not Like "RecycleBin")) OR (((MASTERFAC_WKSPC_MAIN_TABLE_LOC.CLEAN_UWI) Is Not Null) AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.RECORD_SOURCE)="MAXITRAC TANK") AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.SOURCE_STATUS) Not Like "MISSING") AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.NUMERIC_ID) Is Null) AND ((MASTERFAC_WKSPC_MAIN_TABLE_LOC.STATUS_1)="I" Or (MASTERFAC_WKSPC_MAIN_TABLE_LOC.STATUS_1)="O"))
ORDER BY MASTERFAC_WKSPC_MAIN_TABLE_LOC.NUMERIC_ID, MASTERFAC_WKSPC_MAIN_TABLE_LOC.EXTRA_VAL_10, MASTERFAC_WKSPC_MAIN_TABLE_LOC.EXTRA_VAL_6;


That gave me about 15000 rows of data to triage. I'm still at it, finding all sorts of idiosyncrasies in the data. Later on I figured there was another pair of blocks of SOLD's that I couldn't get into that query anyways, so I ended up doing a really clever MATCH thing in xl. I need to check it all tomorrow to make sure I wasn't too clever. That can happen.

Did you know that Access and XL do sorts in subtly different ways? Even after more than a decade of serious XL use in data migrations, I found a new way for it to screw you over on dates. I was wondering why two fields were sorted together, and marked as duplicates. At first I figured they were duplicates elsewhere, but I gradually tracked them all down, and then just these two were left. Finally, the light dawned. It thought the serial number like 1-10-3274 was DATE, for crying out loud, which isn't a big leap because it happens often. XL has a fetish about dates, and I've used many swear words in association with XL and dates. Then it happily figures out how many days it is from when it starts counting dates, and then it uses that value to sort and mark as a duplicate to the actual text value of that number. Sigh.

Just for the record, when I get a choice about doing dates, I always do them like this. Today is 20130605. yyyymmdd. They sort nicely. XL doesn't think it something else and try to be helpful. There are no stupid / or - characters to mess things up. I admit that XL doesn't know how to do date arithmetic with it, but that's XL's problem. If *I* need to do it, there's an app for that.

Lets not even talk about data with leading or trailing spaces. Just don't do it. Ever. Please and thank you. I've never so badly wanted to fix data in the source system, so that my triaging would be ever so much easier. There is so much crap in Serial Number, which is actually EXTRA_VAL_6 in the SQL above, which complicates both the query and the triaging. Still, I suppose that if it wasn't a mess, they wouldn't need me to come along and tell them how to clean it, and in the meantime, get some useful work done with it.

That run yesterday felt pretty good. I was light on my feet, with only the slightest pulls just above my heels. It started really clunky, and then about a minute in it suddenly got better. Overall it felt pretty good, and if I hadn't had someplace to be, I might have run longer. I did not run today. How did I only learn today, that today is national running day? When did that happen?

Tonight was a good core session, working tired hip flexors and calves. Then a great yoga class full of all sorts of zenny yogic goodness.

1 comment:

  1. I am in awe of your XL wisdom, Keith! No where near your level of expertise, I do thank you for the date information - my spreadsheet 'business' requires dates and XL always finds a way to screw up my entry. I'm going to try using your method!!

    National Run Day - who knew? Obviously not you and certainly not me!

    Congrats on the run - glad everything was feeling good :)

    ReplyDelete

Looking forward to reading your comment!