LoTW weirdness

Need help? - Post here and we will find a solution for you.
KC4JNW
Novice Class
Posts: 10
Joined: 08 Aug 2014, 16:15

Re: LoTW weirdness

Post by KC4JNW »

If I understand correctly the "fix" offered up by Mike,W9MDB, fixes the problem with states and not countries. Is my understanding correct?

Jim - KC4JNW
NS8K
Advanced Class
Posts: 61
Joined: 29 Oct 2017, 12:54

Re: LoTW weirdness

Post by NS8K »

Jim,

Mike's SQL statement does fix the state field of records that have the characters // in positions 4 and 5 so you understand that correctly. It is the county and not the country field that also has a problem. That was probably a typo on your part. Mike's statement does not do anything with the county field.

A similar technique could be developed but is more complex because the // characters, which are the start of the comment part of the field we want to remove, will not always be in the same character positions. In Scott county they would be in positions 7 and 8 but in Livingston county they would be in 12 and 13. It would be an iterative process to fix the 2 letter counties then the 3 letter then the 4 letter..... Kind of laborious and somewhat prone to error. On top of that, the next LoTW download will introduce more fouled-up records.

If you can live with it for a while, the ultimate fix will correct all of the "bad" records by doing one LoTW download that goes back to the date where you first had the problem in your log.
73 - Tom NS8K
KC4JNW
Novice Class
Posts: 10
Joined: 08 Aug 2014, 16:15

Re: LoTW weirdness

Post by KC4JNW »

Tom - thanks for your response. I had misunderstood the original issue to be states and countries, not state and counties.

Since I am more concerned about states and countries (from a WSJT-X, JTAlert-X) point of view, I should be safe to do a LOTW download, apply the fix for states and then do another download back to the first week of December to correct the county issue when resolved on the Log4OM side.

Thanks again

Jim - KC4JNW
w6trh
Novice Class
Posts: 19
Joined: 16 May 2015, 04:03

Re: LoTW weirdness

Post by w6trh »

I have been successfully using the SQL update command to correct the SQL log after some number of LOTW updates, but I see on the 19th of Dec that Terry, G4POP, corrected this "// State Name" issue, and was working on a county update.

Did I miss an update for this correction? I am still using the SQL update to fix. Not trying to rush a good thing, just wondering if I missed an update somewhere along the line ?

Tom
W6TRH
NS8K
Advanced Class
Posts: 61
Joined: 29 Oct 2017, 12:54

Re: LoTW weirdness

Post by NS8K »

Tom,

You haven't missed an update. I think Terry was just providing a "status update" saying that the programmer(s) had come up with a solution to the state issue and the next project at hand was to develop a similar fix for the county field.

When this new code is ready and tested I think we might see it in a new release, maybe 1.31.0 along with other program changes. I don't know what all is involved in that process but I'd bet it is not trivial.
73 - Tom NS8K
HB9BRJ
Old Man
Posts: 173
Joined: 23 Nov 2014, 10:46
Location: Schaffhausen

Re: LoTW weirdness

Post by HB9BRJ »

An idea how to cope with different state lengths and white space:

Code: Select all

update log set state=trim(substr(state,1,instr(state,'//')-1)) where state like '%//%'
73, Markus HB9BRJ
User avatar
IW3HMH
Site Admin
Posts: 2925
Joined: 21 Jan 2013, 14:20
Location: Quarto d'Altino - Venezia (ITA)
Contact:

Re: LoTW weirdness

Post by IW3HMH »

I suggest to add a safety check verifying if the DXCC code is USA, Alaska or Hawaii
just to prevent screwing up other data.

This issue is fixed in 1.31 (i'm trying to decode the STATE for those dxcc codes, removing the descriptive part of the name).
A full re-download of LOTW data will fix the DB.
Daniele Pistollato - IW3HMH
w9mdb
Old Man
Posts: 434
Joined: 13 Jul 2014, 12:05

Re: LoTW weirdness

Post by w9mdb »

May i ask when 1.31 will be available?

de Mike W9MDB
w9mdb
Old Man
Posts: 434
Joined: 13 Jul 2014, 12:05

Re: LoTW weirdness

Post by w9mdb »

This does the county too


#0 Back it up!!!
#1 Open QSO Manager/QSO Archive
#4 Click Field Update and click the OK on the "No rows selected" popup.
#5 Under "Execute custom update query" put this
update log set state=substr(state,1,instr(state,'//')-2) where instr(state,'//')>0;
#6 Click all 4 warning boxes and then the "I understand..." button underneath.
#7 Then do this one to fix counties too
update log set cnty=substr(cnty,1,instr(cnty,'//')-2) where instr(cnty,'//')>0;
#8 Click all 4 warning boxes and then the "I understand..." button underneath.

de Mike W9MDB
w0ls
Advanced Class
Posts: 88
Joined: 05 Jan 2015, 21:07

Re: LoTW weirdness

Post by w0ls »

This issue was first reported on 12/14. I have refrained from doing any kind of an LOTW download since that time frame since my log4om database would be messed up. Sure would be nice to have some idea how long before a correction to this problem will be available.

Thanks,
Harry
W0LS
Locked