13Colonies how to make SQL Spot Collector filter


bob-w9zv
 

how can i create a filter on the SCollector that does 

Band and Mode and Callsign LIKE 'K2?'  and Unworked

when selected, i only want to see the 'K2?' stations that on cw or ssb are unworked ??? 

putzed a bit, reading help file... seems to work until i add "unworked"... also would like only spots from the last 30min only...  also how to work in GB13COL WM3PEN TM13COL stations in addition to K2?


Dave AA6YQ
 

+ AA6YQ comments below

how can i create a filter on the SCollector that does 

Band and Mode and Callsign LIKE 'K2?'  and Unworked

when selected, i only want to see the 'K2?' stations that on cw or ssb are unworked ??? 

putzed a bit, reading help file... seems to work until i add "unworked"... also would like only spots from the last 30min only...  also how to work in GB13COL WM3PEN TM13COL stations in addition to K2?

+ Reviewing

http://www.13colonies.us/

+ the objective is to work 15 specific callsigns, on any band in any mode. If that's correct, then

 

1. add each of those 15 callsigns to SpotCollector's Special Callsigns list with the tag 13colonies

https://www.dxlabsuite.com/dxlabwiki/IdentifyLogClubMembers


2. define a filter to find the stations you haven't worked on bands and modes on which you are QRV,:

(TAGS LIKE ('*13colonies*')) and (Bands in (list of bands I can work)) and (Modes in (list of modes I can work))

for example

(TAGS LIKE ('*13colonies*')) and (BAND in ('80m','40m','30m','20m','15m','10m','6m')) and (MODE in ('CW','SSB'))

3. When you work one of these 15 stations, remove it from SpotCollector's Special Callsign List

+ Good hunting!

      73,

              Dave, AA6YQ

 

 


RICH NU6T
 



On Friday, July 1, 2022 at 10:23:38 PM PDT, Dave AA6YQ <aa6yq@...> wrote:


+ AA6YQ comments below

how can i create a filter on the SCollector that does 

Band and Mode and Callsign LIKE 'K2?'  and Unworked

when selected, i only want to see the 'K2?' stations that on cw or ssb are unworked ??? 

putzed a bit, reading help file... seems to work until i add "unworked"... also would like only spots from the last 30min only...  also how to work in GB13COL WM3PEN TM13COL stations in addition to K2?

+ Reviewing

http://www.13colonies.us/

+ the objective is to work 15 specific callsigns, on any band in any mode. If that's correct, then

 

1. add each of those 15 callsigns to SpotCollector's Special Callsigns list with the tag 13colonies

https://www.dxlabsuite.com/dxlabwiki/IdentifyLogClubMembers


2. define a filter to find the stations you haven't worked on bands and modes on which you are QRV,:

(TAGS LIKE ('*13colonies*')) and (Bands in (list of bands I can work)) and (Modes in (list of modes I can work))

for example

(TAGS LIKE ('*13colonies*')) and (BAND in ('80m','40m','30m','20m','15m','10m','6m')) and (MODE in ('CW','SSB'))

3. When you work one of these 15 stations, remove it from SpotCollector's Special Callsign List

+ Good hunting!

      73,

              Dave, AA6YQ

 

 


Jon KM8V
 

In the for what it's worth category, here is the SpotCollector filter I use, without using Special Callsigns or Tags. I'm trying to "triple-play" it, so work each one on digital, phone and CW. I remove the callsign from the appropriate line after I've worked it.

(CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'CW' and <AGEFILTER> and SpotCount > 1) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'SSB' and <AGEFILTER>) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE LIKE 'FT*' and <AGEFILTER> and SpotCount > 1)

And to keep track of it, I built a Google Sheet a few years back, which is available for others to use if you like:

https://km8v.com/u/13colsblank

Here's mine so far this year: https://km8v.com/u/13cols2022

I'll also mention my annual request for SpotCollector leaderboard functionality with an "ignore before date" capability :)

73 de KM8V Jon


bob-w9zv
 

tnx all...

ya, ive been doing it with a simple spreadsheet for years... CW/SSB only as i despise FTx and what its done to 'twitterize' things... 

another is to simply use dxsummit URL, check 'em off one at a time as worked... 

http://www.dxsummit.fi/#/?dx_calls=K2A,K2B,K2C,K2D,K2E,K2F,K2G,K2H,K2I,K2J,K2K,K2L,K2M,GB13COL,WM3PEN,TM13COL
http://www.dxsummit.fi/#/?include_modes=CW&dx_calls=K2A,K2B,K2C,K2D,K2E,K2F,K2G,K2H,K2I,K2J,K2K,K2L,K2M,GB13COL,WM3PEN,TM13COL
http://www.dxsummit.fi/#/?include_modes=PHONE&dx_calls=K2A,K2B,K2C,K2D,K2E,K2F,K2G,K2H,K2I,K2J,K2K,K2L,K2M,GB13COL,WM3PEN,TM13COL 

but i thought i'd be able to readily just have it work like DXCC country does... once worked it automatically comes off the list since no longer "needed"... if i have to manually remove it, i may as well just keep either the spreadsheet/URL solution, as its about the same effort.

but thanks for suggestions.



Dave AA6YQ
 

On Sat, Jul 2, 2022 at 04:17 AM, Jon KM8V wrote:
In the for what it's worth category, here is the SpotCollector filter I use, without using Special Callsigns or Tags. I'm trying to "triple-play" it, so work each one on digital, phone and CW. I remove the callsign from the appropriate line after I've worked it.

(CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'CW' and <AGEFILTER> and SpotCount > 1) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'SSB' and <AGEFILTER>) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE LIKE 'FT*' and <AGEFILTER> and SpotCount > 1)
+ That expression can be simplified to
 
(CALLSIGN IN ('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL')) and <ORIGINFILTER> and <AGEFILTER> and ((MODE = 'SSB') or (((MODE = 'CW') or (MODE LIKE 'FT*')) and (SpotCount > 1)))
 
+ which has the advantage of reducing the "work" required after logging a QSO with one of those stations to deleting one instance of a callsign.

 
And to keep track of it, I built a Google Sheet a few years back, which is available for others to use if you like:

https://km8v.com/u/13colsblank

+ Using the <REPORT> and <APPENDREPORT> commands, you can define a DXKeeper script that generates a progress report by filtering the Log Page Display for QSOs with those 15 stations in each of the "modes of interest", sorted by callsign.

       73,

             Dave, AA6YQ

 


Dave AA6YQ
 

+ AA6YQ comments below
but i thought i'd be able to readily just have it work like DXCC country does.
+ Implementing Realtime Award Tracking for a 1-week activity held once per year that involves working ~15 stations is not justified, given SpotCollector's filtering capabilities.

      73,

           Dave, AA6YQ


Dave AA6YQ
 

# More AA6YQ comments below
In the for what it's worth category, here is the SpotCollector filter I use, without using Special Callsigns or Tags. I'm trying to "triple-play" it, so work each one on digital, phone and CW. I remove the callsign from the appropriate line after I've worked it.

(CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'CW' and <AGEFILTER> and SpotCount > 1) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'SSB' and <AGEFILTER>) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE LIKE 'FT*' and <AGEFILTER> and SpotCount > 1)
+ That expression can be simplified to
 
(CALLSIGN IN ('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL')) and <ORIGINFILTER> and <AGEFILTER> and ((MODE = 'SSB') or (((MODE = 'CW') or (MODE LIKE 'FT*')) and (SpotCount > 1)))

# The suggested simplification is appropriate if your goal is to work those 15 stations once in any mode. If the goal is to work them in each of CW, SSB, and FT*, then your original expression is appropriate.

         73,

              Dave, AA6YQ


Jim N7US
 

I’ve used SpotCollector SQL filters for the 13 Colonies, and they’ve worked well.

 

This year I decided to use N1MM with a User-Defined Contest module for 13 Colonies.  I’m trying to work each of the stations on CW on each band, and N1MM with its Available window is working well for that.

 

https://n1mmwp.hamdocs.com/mmfiles/13colonies-zip/

 

I’ll import the N1MM log into DXKeeper afterwards as I do with all contest logs.

 

73,  Jim N7US

 

From: DXLab@groups.io <DXLab@groups.io> On Behalf Of Dave AA6YQ via groups.io
Sent: Saturday, July 2, 2022 11:52
To: DXLab@groups.io
Subject: Re: [DXLab] 13Colonies how to make SQL Spot Collector filter

 

# More AA6YQ comments below

In the for what it's worth category, here is the SpotCollector filter I use, without using Special Callsigns or Tags. I'm trying to "triple-play" it, so work each one on digital, phone and CW. I remove the callsign from the appropriate line after I've worked it.

(CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'CW' and <AGEFILTER> and SpotCount > 1) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'SSB' and <AGEFILTER>) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE LIKE 'FT*' and <AGEFILTER> and SpotCount > 1)

+ That expression can be simplified to

 

(CALLSIGN IN ('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL')) and <ORIGINFILTER> and <AGEFILTER> and ((MODE = 'SSB') or (((MODE = 'CW') or (MODE LIKE 'FT*')) and (SpotCount > 1)))

# The suggested simplification is appropriate if your goal is to work those 15 stations once in any mode. If the goal is to work them in each of CW, SSB, and FT*, then your original expression is appropriate.

         73,

              Dave, AA6YQ


Jon KM8V
 
Edited

On Sat, Jul 2, 2022 at 12:51 PM, Dave AA6YQ wrote:
# The suggested simplification is appropriate if your goal is to work those 15 stations once in any mode. If the goal is to work them in each of CW, SSB, and FT*, then your original expression is appropriate.
I was bored earlier, waiting for some needed 13 colonies spots to arrive, and I updated my tracking google sheet to generate the SpotCollector filter based on what remains needed - the goal for this is to "triple play" each station.

If anyone else might find that useful, the updated sheet is here: https://km8v.com/u/13colsblank

If you scroll down, you'll see a generated SQL filter for SpotCollector based on what is represented in the sheet above.

And if anyone would like to see my current sheet for 2022, it is here: https://km8v.com/u/13cols2022 

73 de KM8V Jon


Jon KM8V
 

On Sat, Jul 2, 2022 at 12:49 PM, Dave AA6YQ wrote:
+ Implementing Realtime Award Tracking for a 1-week activity held once per year that involves working ~15 stations is not justified, given SpotCollector's filtering capabilities.
I don't care about realtime award tracking, but the ability to define an arbitrary leaderboard in spotcollector with an "ignore qsos before date" option would be pretty cool. :)

73 de KM8V Jon


Dave AA6YQ
 

+ AA6YQ comments below
I don't care about realtime award tracking, but the ability to define an arbitrary leaderboard in spotcollector with an "ignore qsos before date" option would be pretty cool. :)

+ What is an "arbitrary leaderboard"?

       73,

              Dave, AA6YQ


Jon KM8V
 

On Sat, Jul 2, 2022 at 06:21 PM, Dave AA6YQ wrote:
+ What is an "arbitrary leaderboard"?
Perhaps I should have said, "arbitrarily named leaderboard". A way to tag something as a leaderboard with a specific scope, and the aforementioned ignore-before-date.

The obvious example being 13 colonies, being able to flag the special event callsigns as a "leaderboard", separate from the normal <Leaderboard> tag, and specifying to ignore QSOs prior to Jan 1 2022 (or June 30 2022, whatever) in the log.

73 de KM8V Jon


Dave AA6YQ
 

# more AA6YQ comments below

On Sat, Jul 2, 2022 at 06:21 PM, Dave AA6YQ wrote:

+ What is an "arbitrary leaderboard"?

Perhaps I should have said, "arbitrarily named leaderboard". A way to tag something as a leaderboard with a specific scope, and the aforementioned ignore-before-date.

# What do you mean by "leaderboard"?

# What's an example of a "specific scope"?

The obvious example being 13 colonies, being able to flag the special event callsigns as a "leaderboard", separate from the normal <Leaderboard> tag, and specifying to ignore QSOs prior to Jan 1 2022 (or June 30 2022, whatever) in the log

.# Assigning the Leaderboard tag to a callsign on the Special Callsign List means that Spot Database Entries for that callsign will be considered needed if the callsign hasn't been worked on the entry's band (160m, 80m, 40m, 30m, 20m, 17m, 15m, 12m, 10m, 6m, 2m) and in the entry's mode (phone, CW, digital). The word "leaderboard" refers to a display provided by Club Log or by a DXPedition - not something that SpotCollector provides. The Leaderboard tag would only be useful to "13 colonies" if your goals is to work all 15 of those stations on 160m, 80m, 40m, 30m, 20m, 17m, 15m, 12m, 10m, 6m, 2m and in phone, CW, and digital.

    73,

          Dave, AA6YQ


Jon KM8V
 

On Sun, Jul 3, 2022 at 01:28 AM, Dave AA6YQ wrote:

The obvious example being 13 colonies, being able to flag the special event callsigns as a "leaderboard", separate from the normal <Leaderboard> tag, and specifying to ignore QSOs prior to Jan 1 2022 (or June 30 2022, whatever) in the log

.# Assigning the Leaderboard tag to a callsign on the Special Callsign List means that Spot Database Entries for that callsign will be considered needed if the callsign hasn't been worked on the entry's band (160m, 80m, 40m, 30m, 20m, 17m, 15m, 12m, 10m, 6m, 2m) and in the entry's mode (phone, CW, digital). The word "leaderboard" refers to a display provided by Club Log or by a DXPedition - not something that SpotCollector provides. The Leaderboard tag would only be useful to "13 colonies" if your goals is to work all 15 of those stations on 160m, 80m, 40m, 30m, 20m, 17m, 15m, 12m, 10m, 6m, 2m and in phone, CW, and digital.

Yes. Exactly. Although this year I decided just to try to triple-play 13 Colonies, not work them all everywhere.

I had another example of where I thought this would be useful, but it has escaped me at the moment.


Gordon LaPoint
 

When I use either filter all I get is the SSB spots, even though I see several for CW if I don't use any filter.
What have I done wrong??
Thanks,
Gordon - N1MGO

On 7/2/2022 12:51 PM, Dave AA6YQ wrote:
# More AA6YQ comments below
In the for what it's worth category, here is the SpotCollector filter I use, without using Special Callsigns or Tags. I'm trying to "triple-play" it, so work each one on digital, phone and CW. I remove the callsign from the appropriate line after I've worked it.

(CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'CW' and <AGEFILTER> and SpotCount > 1) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE = 'SSB' and <AGEFILTER>) or (CALLSIGN IN('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL') and <ORIGINFILTER> and MODE LIKE 'FT*' and <AGEFILTER> and SpotCount > 1)
+ That expression can be simplified to
 
(CALLSIGN IN ('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL')) and <ORIGINFILTER> and <AGEFILTER> and ((MODE = 'SSB') or (((MODE = 'CW') or (MODE LIKE 'FT*')) and (SpotCount > 1)))

# The suggested simplification is appropriate if your goal is to work those 15 stations once in any mode. If the goal is to work them in each of CW, SSB, and FT*, then your original expression is appropriate.

         73,

              Dave, AA6YQ



bob-w9zv
 

jon KM8V -   were you thinking of the RT66 On The Air radiosport event in Sept?    its a similar thing with a list of specific callsigns like 13Colonies?  like you, i was bored with tracking it manually... 

Jim N7US - ya i did the N1MM thing as well, it does provide a subtractive "need" list, but no help in selectively filtering spots so only the ones i yet still need are displayed to be able to simply "click to tune" to them.   as you have noticed hundreds of spots for the 13Colonies  occur, mostly i suspect because folks likely have their loggers to "auto-spot" what they work.

its easy to track/do manually of course...  after all, there are only 16 stations.... 

but thought as long as i have this Atomic Powered Swiss Army Knife id try to use it.   its almost the epitome of lazy  :-)) 


Jim N7US
 

I used DXLab for 13 Colonies for several years, using an SQL expression that I edited after each time I worked a new station.  It’s worked very well.

 

The information below is more appropriate for the N1MM reflector, and I’ve posted it there.

 

This year I’m using N1MM with a User Defined Contest file, special list of sections (the 13 states plus G and F), and a Call History File with only the 16 stations included.  With N1MM configured correctly, the stations already worked on each band, which is different than just working each station once, are colored grey in the band map.  Those available to be worked that are currently active appear on the bandmap and the available window, the latter being the more useful.

 

It's important to use the N1MM call history file with only the 16 stations included.  In the telnet window check the box to only show spots of stations in the call history file.

 

N1MM allowed me to work each needed/unworked station (on each band) when it is spotted.  I have worked about 80 stations, all on CW, which is not the intent of the activity, but I’ve found it to be fun.  To just work each station once, DXLab with the SQL works well.

 

I import each N1MM log into DXLab, which has been my general logging program for many years.

 

Jim N7US

 

 

From: DXLab@groups.io <DXLab@groups.io> On Behalf Of bob-w9zv via groups.io
Sent: Monday, July 4, 2022 07:36
To: DXLab@groups.io
Subject: Re: [DXLab] 13Colonies how to make SQL Spot Collector filter

 

jon KM8V -   were you thinking of the RT66 On The Air radiosport event in Sept?    its a similar thing with a list of specific callsigns like 13Colonies?  like you, i was bored with tracking it manually... 

Jim N7US - ya i did the N1MM thing as well, it does provide a subtractive "need" list, but no help in selectively filtering spots so only the ones i yet still need are displayed to be able to simply "click to tune" to them.   as you have noticed hundreds of spots for the 13Colonies  occur, mostly i suspect because folks likely have their loggers to "auto-spot" what they work.

its easy to track/do manually of course...  after all, there are only 16 stations.... 

but thought as long as i have this Atomic Powered Swiss Army Knife id try to use it.   its almost the epitome of lazy  :-)) 

_._,_._,_


Dave AA6YQ
 

+ AA6YQ comments below
When I use either filter all I get is the SSB spots, even though I see several for CW if I don't use any filter.
What have I done wrong??

+ Please post the filter you are using.

     73,

         Dave, AA6YQ


Gordon LaPoint
 

I'm trying the following SQL filter in SpotCollector, and its showing SSB spots only.

(CALLSIGN IN ('K2A','K2B','K2C','K2D','K2E','K2F','K2G','K2H','K2I','K2J','K2K','K2L','K2M','WM3PEN','GB13COL','TM13COL')) and <ORIGINFILTER> and <AGEFILTER> and ((MODE = 'SSB') or (((MODE = 'CW') or (MODE LIKE 'FT*')) and (SpotCount > 1)))

I'm using the following SQL filter to see only the ones I have not worked, and its working fine
(CALLSIGN IN ('K2D','K2E','K2F','K2I','K2K','WM3PEN','GB13COL','TM13COL'))

Thanks for the help!
Gordon - N1MGO


On 7/4/2022 12:24 PM, Dave AA6YQ wrote:
+ AA6YQ comments below
When I use either filter all I get is the SSB spots, even though I see several for CW if I don't use any filter.
What have I done wrong??

+ Please post the filter you are using.

     73,

         Dave, AA6YQ