Beyond 20/20

TABLE OF CONTENTS

• IVS - Definition section 
  • Example
• IVS - Field Definitions section
  • MissingValues
• IVS - User Definitions section
  • Constant Value
  • Field Names
• IVS - Field Groupings section
  • Example
• IVS - Field Recode section
  • Example

A source definition file (IVS) defines the format of a table's source file and, in the case of an ASCII file, describes its layout. The IVS also contains information about series definitions, user-defined fields, and redefinition fields.

IVS - Definition section 

The mandatory Definition section of an IVS specifies the version of the IVS, the filter module that will be used to process the source file, and the field definitions (lengths of the records) for ASCII source files.

Beyond 20/20 uses three kinds of source files:

• ASCII (fixed record text)
• DBF (dBase file format)
• CSV (Comma-separated values)

DBF and CSV files do not need field definitions, because the field information is contained within the files themselves.

The following describes the key entries in the definition section of an IVS:

Example

This Definition section of an IVS file specifies that the source file is a fixed length ASCII text file that uses the ANSI character set. Its records are delimited by carriage return/line feed pairs.

[Definition] 
RecLen=AutoCRLF 
Filter=IVTEXT.DLL 
Version=2

IVS - Field Definitions section

The Field Definitions section of an IVS defines all the fields in an ASCII source file. (Only used for ASCII files. DBF and CSV files do not need field definitions because they contain their own field information.)

The syntax for a field definition is as follows:

Fieldname=DataType,Start,Width[,DateFormat,Treatment,DefinitionFile,Description,Periodicity,Decimals,Category,MissingValues,LookupField]

Fieldname is the name of the source field. Field names can use any characters, including spaces, but ideally they should begin with a letter and all the other characters should be either letters or numbers. The maximum length of a field name is 15 characters.

Only DataType, Start, and Width are required values, with the following exceptions:

• If the data type is Date, DateFormat must be defined.
• If the data type is Zoned, Decimals must be defined.

The commas (and no spaces) between the values are required. Note that if one or more undefined fields precede a defined field, you must mark the places for the undefined fields with empty comma pairs (",,"). However, comma pairs are not required for undefined fields that fall at the end of their code string.

The following table describes the values in the Field Definitions section of an IVS:

MissingValues

A missing value is a value that is either not present in the source file, or that is present but is defined as 

being “missing.” Missing values can be indicated by blank cells or by zeros or other characters.

Treatment of missing values differs between Beyond 20/20 version 4.1 and version 5.2. Both treatments are described below:

An example of a field definition that includes a missing value definition follows:

Income=Numeric,11,4,,,,,,,, =1;99999=2;-99998=3;NULL

• As in the example for version 4.1, Income is the field name. Its type is Numeric, it starts at the 11th character, and its width is 4 characters. The seven empty comma spaces represent undefined optional values.
• The final four entries assign missing value definitions to the numeric keys "1," "2," and "3."
• A blank space of any length in a field refers to the missing value definition that corresponds to the numeric key "1."
• "99999" refers to the missing value definition that corresponds to the numeric key "2."
• "-99998" refers to the missing value definition that corresponds to the numeric key "3."
• Because NULL has no qualifier, it defaults to the numeric key "1."

IVS - User Definitions section

The optional User Definitions section of an IVS can be used in three ways:

• To define a field whose value is constant.
• To extract a value from a filename.
• To redefine a field's type in a dBase file.

The syntax for entries in this section is as follows:

Fieldname=Type,Value[,DateFormat]

• Fieldname is the name of a user-defined source field. Field names can use any characters, including spaces, but they should begin with a letter and all the other characters should be either letters or numbers. Following this rule makes it easier to specify derived fields, constraints, and other calculations. The maximum length of a field name is 15 characters.

The values in the User Definitions section of an IVS are described below:

Constant Value

A constant value is used when a field has one single value for the entire file.

In the following example, a constant value field has been created to help with processing monthly employee records. The field's code is Month, its type is Date, and its value is Aug2000. "mmmyyyy" is the date format that has been applied to the date Aug2000.

[User Definitions] 
Month=Date,Aug2000,mmmyyyy

Now when a table is built from this IVS, the value of the Month field will automatically be set to Aug2000.

Constant values can also be used to extract descriptive information that is not included within the record information. For example, while the above constant value field is useful in August 2000, the value of Month will have to be updated every month in the IVS in order to process the current employee records.

If the filename of the source file always contains the date, you can extract the date information from that name by defining filename parameters. Filename parameters act as placeholders for the actual information.

For example: If the file name of the file that contains the employee records is always EMPmmyy.dbf, where "EMP" stands for "Employee Records" and "mmyy" is the month and year, you can define the filename parameter: "???mmyy."

Now the following user-defined field definition will extract the date from the file name and insert it into the constant value field "Month."

[User Definitions] 
Month=Date,{month}{year},mmmyyyy

See help for Filename Parameters ( or 303 in the Builder User's Guide) for a list of possible variables.

Field Names

A field name (enclosed in square brackets) is used to redefine a dBase field that has been stored with the incorrect type, such as a date that does not conform to Beyond 20/20 standard date formats.

You can redefine the date field by defining a new field name (such as Date2). Define its type as Date and then include the old fieldname in square brackets as the value for the new field, as follows:

[User Definitions] 
Date2=Date,[DATE],yyyymmdd

• Date2 is the filename of a user-defined field.
• The Type of date2 is Date and its format is yyyymmdd.
• The value of Date2 is [DATE], which refers to an existing but wrongly-formatted DBF field.
  Now Date2 will derive its data from the DBF field DATE and will format it correctly as a Beyond 20/20 Date field.

IVS - Field Groupings section

The Field Groupings section of an IVS defines a series field. A series field is a group of fields that can be displayed together in a table as one dimension.

The syntax to define a series field is as follows:

FieldName=CodeValue,SourceField,Override

FieldName is the name of a field that is to be defined as a series field.

The following describes the values in the Field Groupings section of an IVS:

Example

The following is the Field Groupings section of an IVS file. It creates a series field called "Year" that contains data for both 2000 and 2001.

[Field Groupings] 
Year=2000,00,Numeric 
Year=2001,01,Numeric

• Year is the name of the newly-defined series field.
• 2000 and 2001 are codes for the two items in the series.
• 00 and 01 are the source fields that contain the values for 2000 and 2001.
• Data for use in series fields must be numeric or boolean. If it is not, its type can be overridden. In this example, Numeric overrides the former type of the data in the year source fields, which was Character.

IVS - Field Recode section

The Field Recode section of an IVS is used to define a redefinition field so you can convert a code from one value to another.

To redefine a field, first use Visual Builder to create an IVD that defines the following two fields:

• The Lookup Field, whose codes match the source codes that are to be converted.
• The Value Field, which contains the values that the codes will be mapped to.

Once you have created the IVD, the syntax to define a redefinition field is as follows:

FieldName=SourceField,LookupField,ValueField,Filename

The following describes the values in the Field Recode section of an IVS:

Example

[Field Recode] 
Region=RegionCode,Code,NewCode,C:\B2020\Data\Region.ivd

• Region is the name of the new field that will be created.
• RegionCode is the source field - the field in the source file that contains the codes that are to be converted.
• Code is the lookup field - the field in an IVD that matches the codes in the source field.
• NewCode is the value field - the field in the IVD that contains the new values the codes will be mapped to.
• C:\B2020\Data\Region.ivd is the path to the IVD that contains the redefinition information.