Define Statistics

---

  Up   Back  Next  Example

SWISSLOG includes several predefined statistics that are stored in the SWLSTAT.MDB database. These statistics are called Standard Statistics. The SWLSTAT.MDB contains the statistic definitions and the information tables. You cannot modify this DB. Whenever an update of information tables or statistics is available, the DB will be replaced.

If you need a statistic that is not predefined you can create a Private Statistic. Contrary to Standard Statistics these Private Statistics are stored in your log database.

If you want to modify a Standard Statistic, you must first export it and then import it as Private Statistic. Then you should change the name and deactivate the corresponding Standard Statistic.

Because only a limited number of fields are available to store statistic information, field conflicts can exist. Field conflicts are detected at run time. If you define Private Statistics you will get an error message when entering a QSO. You then have to deactivate one of the conflicting Statistics, use another field, Define the Statistic as Sub Statistic if it usses the same Key as an other Statistic  or use a variable statistic. There is a function provided to reassign a new field for a statistic see resolve statistic conflicts.

Import a Statistic Definition

The SWISSLOG statistic definition files have use the following file extension – .INS and are usually located in the Info directory. However, it is also possible to obtain statistic definitions from other sources, such as the internet. In that case, you must remember where you stored them.

To import a statistic select File ¦ Statistic Import / Export ¦ Import Statistic from the main menu. 

The following dialog is displayed:

• In the Directory Box select the directory which contains the statistic definitions.

• In the Import-File box select the statistic definition file for the statistic you want to import.

• The press the Import button.

Please see Predefined Statistics for an overview about the statistics, the fields used, and the selection criteria.

Create a Statistic Definition

Defining a statistics is a task that requires some experience and needs some planning. It will therefore be explained step by step using an example. We will define a DOK statistic. DOK is the abbreviation for Local Amateur Group (Section) in Germany. Note: DOK is already defined as standard Statistic, it is only being used here to illustrate how statistics are defined.

Concept used for Statistics

Before we start with the description of the Statistic Definition – one fundamental concept of the SWISSLOG statistic needs to be explained. The following briefly explains how SWISSLOG selects the QSO's which are included in a statistic.

QSO's included in the statistic

Most of the Statistic Definitions contain a SQL Query that selects the QSO's for the statistic. Note: Not all statistic definitions contain a SQL Query – a SQL Query is only needed if, not all QSO's count for a statistic; as an example the DXCC statistic does not have an SQL query. If the statistic does include a SQL Query, you can define exactly which QSO's count for the statistic. The Query is then used to select the QSO's that are used to calculate the statistic. This concept makes it possible to use the same field for several different statistics. A very good example of a multiple use field is the Region field. Regions are unique to countries. Therefore, we can put the region information for all countries into the same field and still have different statistics for each country. In the Query, you can define the QSO's for a specific DXCC that count for the statistic. For example you would specify:

DXCC = 'DL' for DOK Statistic

DXCC = 'HB9' for the H26 Statistic

DXCC = 'F' for the DDFM Statistic

DXCC = DXCC IN ('EA', 'EA6', 'EA8', 'EA9') for the TPEA Statistic

The same Query is also used to determine which statistics a QSO qualifies for when you enter new QSO's. This makes it possible to:

• Dynamically change the label of the field – The label is defined in the Prompt field in the Statistic Definitions

• Associate an information table with a field – This is defined in the field-group named Information - Table in the statistic Definition. This allows you to associate different information tables with the same field, for example:

  • the DOK Info-Table is associated to DL QSO's

  • the Kanton Info-Table is associated to HB9 QSO's

  • the DDFM Info-Table is associated to F QSO's

  • the TPAE Info-Table is associated to EA, EA6, EA8 and EA9 QSO's

The SQL Query can be defined with the help of the report generator – so, you don't need to know how to program using SQL.

Select the Field which contains the Statistic Information

First you need to define the field that contains the data the statistic is based on. See Fields for Award Information for information on how to select a field.

We will put the DOK into the field P_Sub_Region, which is in the PQTH Table. This corresponds to the entry field Sub_Region on the QTH page of the Edit QSO window. 

Create an Information Table for the Statistic

The following step is optional:

Information Tables (also referred to as Info-Table) contain detailed information about the statistic items. For example the IOTA Info-Table contains the full name for each island abbreviation. If you provide an Info-table with the detailed information, it can be displayed in the statistic views and reports, and you can look up the information when entering QSO's.

For this example we'll assume we have already created a DOK Info-Table. See How to create Information-Tables for detailed information.

Create the DOK Statistic

From the main menu select Edit ¦ Define Statistics. This will open the Create/Change Statistic Definitions view.

• Select the Overview page.
  
  
  
  On the upper half of the page is a listing of the statistics that have already been defined. By default is empty. To see the standard statistics you have to select the checkbox which is located below (as shown in the image)
  
  
• Press the New button
  
  This opens the page where you can define a new statistic. Next, fill in the fields so that the result will look similar to the example shown below: 

  

  The following table lists the fields and the values you must put into them:

  Field	Value	Explanation
  Name	DOK	This is the name you want to assign to the statistic.
  Variable Statistic	not checked
  Sub_Statistic	not checked	Sub-Statistics are Statistics that are based on Fields and Values which are also used for other statistics. Examples are the different IOTA Statistics. The IOTA Statistic counts all IOTA's and is defined as a Main-Statistics. All other IOTA-Statistics like IOTA-EU are defined as Sub-Statistics because they use a subset of the Main-Statistic (only the EU-IOTA's).
  Log-Table	PQTH	This is the table that contains the field on which the statistic is based. If you don't know the name of the table, you should consult the help file or just select a table and open the Field box. It will contain all fields of the selected table. The field we use is in the PQTH (Partner-QTH) table.
  Field	P_SUB_REGION	The DOK will be put into the Sub_Region field.
  Start in Field		If only a part of the field is used to build the statistic, you can specify at which position the relevant part starts. An example is the Grid square statistic, where only the first four characters of the QTH-Locators are used for the statistic.
  Field length		Here you can define the number of relevant characters in the field, starting at the position defined in Start in Field.
  Statistic-Table		Used for variable statistics – name of the table that stores the fields for a variable statistic
  Prompt	DOK	This is a label that is displayed at the place of the default label on the QSO entry mask if the QSO qualifies for the DOK-statistic. The Check-SQL is used to check if a QSO qualifies for the statistic.
  QSL-Accepted		Indicates if QSL via LOTW or eQSL is accepted for this statistic. For example LOTW QSL are accepted for the DXCC Awards, therefore LOTW should be checked for the DXCC Statistic. NOTE: eQSL confirmations count only if station is eQSL AG member. Swisslog automatically handles this info during eQSL synchronization and when the membership panel is active.
  Check-SQL	press the Create button	This is the SQL which selects the QSO's which qualify for the statistic. This SQL code is created with help of the report generator. For the DOK statistic only QSO's with stations in Germany qualify. Therefore we have to specify a query which selects only stations from Germany. The easiest way to accomplish this is to check if the DXCC for the station is 'DL'. See Create Check-QSL below for details.
  Information - Table The following fields are used to define the information table that contains the description of the DOK's. If an information table is specified, then the description can be displayed together with the statistic key. It is also possible to search for the correct value when adding and/or editing QSO's. Therefore, you should create an information table whenever possible. The information table must exist before you can enter the following fields. For details see: How to create Information-Tables The DOK information table contains two fields – the DOK and the Description – such as: A01,Konstanz A02,Bruchsal A03,Baden-Baden A04,Buehl
  Database	SWISSLOG	This is the database which contains the information table. It is normally the SWISSLOG DB.
  Table	INF_DOK	The name of the table containing the DOK information. Information tables names are always prefixed with "INF_"
  Key Field	DOK	This is the field containing the DOK
  Description	DESCRIPTION	This is the field containing the description of the DOK

This completes the definition of the DOK statistic.

Press the save button and close the statistic definition dialog.

Create Check-SQL

To create the Check-SQL press the button and

• press the button and add the PQTH table

• close the add table dialog

• select P_DXCC from the PQTH table and drag it to the grid

• double-click on the P_DXCC Field and the Value Editor Window will open

• select the Filter Tab

• in Number select Filter 1

• in Command select 'Is Equal To'

• in Value 1 enter DL

• close the Value Editor

• close the report generator

• reply Yes to save the Report

Variable Statistic

Variable statistics allow you to create statistics with fields that do not exist in the QSO! Actually each variable statistic represents a new QSO field (a 32 character string).

For each variable statistic, a table is created which contains one value field and a reference to the QSO it belongs to (QSONr.).

Use this type of statistic only if there is no way to use either the Diploma, Spec or Sub-region fields.

Top   Up    Back  Next