Beyond 20/20

TABLE OF CONTENTS

• IVU - Definition section
• IVU - Input section
  • Input Section Examples
• IVU - Output section
  • Dimensions
    • Method 1
    • Method 2
  • Output Section Example
• IVU - Output-xxx section
• IVU - Units section
• IVU - Rules section
• IVU - Notes section
• IVU - Record Constraints section
  • Numeric Records
  • Coded Records
    • AND and OR
    • Key Entries
    • Fieldname
    • Fieldtype
    • Levels
    • Items
  • The [Q-FieldN] Sections
    • Example of a record constraint
• IVU - Missing Values section
  • Examples
    • Example 1
    • Example 2: Missing Values in Multilingual Tables
• IVU - Footnotes section
• IVU - FootnoteLoader section
  • Footnote Loader Only definition

An update definition file (IVU) is the primary input to the Integrator's Builder. It defines the steps needed to create or update a table or extract, and it also specifies a source definition file and (optionally) one or more dimension definition files.

You can create an IVU in Visual Builder when you are creating a new table or extract. The Builder will prompt you to save the IVU.

Section names in an IVU appear in square brackets before the key entries, as in this example:

In this example, the section name is Input. FilePath, Definition, and FilenameParameters are keys. The value for each key appears after the equals sign.

Footnote Loader Only definition

An IVU is a valid "footnote loader only IVU" if it contains a valid FootnoteLoader section and a Name key specifying an existing IVT file. There should be no Rules specified.

• When a valid "footnote loader only IVU" is loaded into the Builder, the Data Load Footnotes menu option will become available. If processed by the Integrators Builder, it will run only the Footnote Loader section of the Builder.

IVU - Definition section

Starting in Builder 7.1, this section contains only a single key which indicates the current version of the IVU file. For example,

Version=3

IVU - Input section

The Input section of an IVU identifies the source files for the build. 

• Files that are not in the same folder as the IVU must include complete path information.

The following table describes the key entries in the Input section of an IVU:

Input Section Examples

The following excerpt from an IVU specifies a single ASCII file as the input for a build. It names both the ASCII source file (SIC1992.TXT) and an IVS file that defines the ASCII file (SIC.IVS). No path information is required, because both files are in the same folder as the IVU.

[Input] Definition=SIC.IVS 
FilePath=SIC1992.TXT

The following Input section might be used to process several files. This example includes path information because the required files are not in the same folder as the IVU. Wildcards are used in the FilePath definition to indicate that any files in C:\XDATA whose names start with "SIC," are followed by any four characters, and end with a .TXT extension can be used as source files.

[Input] Definition=C:\XDATA\SIC.IVS 
FilePath=C:\XDATA\SIC????.TXT

The following Input section builds a table from an extract:

[Input] Definition=IVPLUS.DLL FilePath=C:\XDATA\92GR.IVX Source=C:\XDATA\92GR.IVX

A dBase source file contains all the field definitions and does not require an IVS file. In the following example, the Definition key names the DLL that is used to process dBase files. The FilePath key then names the dBase file. Because the DBF file is not in the same folder as the IVU, complete path information is given.

Note that if any user defined fields, series, or redefinitions are defined for a dBase file, the Definition key must contain the name of a source definition file (IVS).

[Input] Definition=IVDBASE.DLL FilePath=C:\XDATA\EMPLOYEE.DBF

IVU - Output section

The Output section of an IVU defines the table or extract that will be created.

Multilingual tables also require an Output-xxx section for each language, where xxx is a 3-letter language code. Output-xxx sections have many of the same key entries as Output sections, but key entries in an Output-xxx section override corresponding entries in an Output section.

The following describes the key entries in the Output section of an IVU: 

Dimensions

There is one dimension key for each dimension in the table. Keys are numbered from Dim1 to DimN, where N is the number of dimensions in the table. 

There are two methods for defining dimension keys:

Method 1

DimN=Name,IVD

• This method uses a dimension definition file (IVD or IVN) to define all aspects of the dimension. 

The following describes the values for DimN in the Output section of an IVU, using Method 1:

Method 2

DimN=Name,Description,Method,Type[,Periodicity]

• This method does not use an IVD. It requires you to specify all the dimension information within the key entries. 

The following describes the values for DimN in the Output section of an IVU, using Method 2:

Output Section Example

In the following example, the rows will contain the Region dimension with the Sex dimension nested. The string 2,1 corresponds to the dimensions associated with the Dim2 and Dim1 keys specified above. The columns will contain the Date dimension (Dim3) and the other dimensions will be empty because the string is empty.

[Output]Name=table.ivtDim1=Sex,"Gender",Add,NormalDim2=Region,"Region",Add,NormalDim3=Date,"Date",Add,NormalIfFileExists=ReplaceIfDataExists=AddLanguage1=fraLanguage2=engCalculate=FalseDecimals=0ReadOnly=FALSERowDims=2,1ColDims=3OtherDims=

IVU - Output-xxx section

"xxx" refers to a 3-letter language code. The LanguageN key must be defined in the [Output] section or this language specific section will be ignored. Anything specified in this section overrides corresponding values specified in the [Output] section. 

The following describes the values for the Output-xxx sections of an IVU:

IVU - Units section

Used when creating a table from an extract. 

The optional Units section of an IVU defines a series whose values are derived by performing a statistical calculation on the values in one or more numeric source fields. 

Key entries in the Units section are as follows:

ItemN=Name_of_item,{Source Field},{Calculation}

IVU - Rules section

The Rules section of an IVU defines links between source fields and output dimensions. It also allows you to initialize a table for missing values, define start and end dates for a time dimension, and define a weight field. (The Rules section is not used when creating an extract.)

The following describes the key entries in the Rules section of an IVU:

IVU - Notes section

The Notes section of an IVU holds comments about the information in the file. Notes are copied to the table or extract summary during the build process.

The following describes the key entries in the Notes section of an IVU:

Rules for entering notes:

• Start lines with a semicolon (;) to identify them as notes.
• End lines with a plus sign (+) to make them wrap.
• You can use HTML in notes if you want to create links to other files. However, Beyond 20/20 urges caution. See HTML in Summaries in the Builder Help for more information.

IVU - Record Constraints section

Record constraints determine which records of a source file will have their data constrained when they are loaded into a table, and in what way the data will be constrained. 

• Record constraints can be created in the Visual Builder by choosing Record Constraints from the Data menu.

The RecordConstraint section of an IVU contains all the constraints that have been defined. 

Source records are constrained in different ways, depending on whether their data is numeric or coded. 

• Any or all of the following constraint types can be combined to create a single record constraint.
• One record constraint can involve more than one operation and data from more than one source field.
• There must be a dimension definition file (IVD) for the source field that is used as a record constraint. 

Numeric Records

Numeric records are constrained by applying either a range constraint or a comparison operator to their data.

• A range constraint defines two values. The data that falls in between the defined values will be included in the table or extract.
• A comparison operator specifies which values to include in the table or extract by comparing the values to one defined value. The following operators are used:
  = Equals
  < Less than
  <= Less than or equal to
  > Greater than
  >=  Greater than or equal to

Examples of comparisons:

Coded Records

Coded records are constrained by selecting specific items to include. 

• For example, if a source field called Size has three possible values - small, medium, and large - you can constrain the data to include only the small items.

AND and OR

AND and OR are methods of combining separate fields to create a complex record constraint. (Fields that are ANDed or ORed together may already have had range or comparison constraints applied to them.)

• ANDing fields together includes only the data that falls in both fields.
• ORing fields together includes all the data that falls into either field. 

Key Entries

There are two key entries in a Record Constraint section: Constraints, and FieldN. Both entries are required.

• The Constraints key has a numeric value that represents the number of source fields that will be used in creating a record constraint. It is the same as the maximum value of N in FieldN.

  For example: "Constraints=2."

• N is a sequential number for a source field whose data is used in creating a record constraint. The first source field is Field1.

The syntax for the FieldN key is as follows:

FieldN=Fieldname,Fieldtype,Levels,Items

The following describes the values for the RecordConstraint section of an IVU:

Fieldname

The name of a source field that will be used in creating a record constraint.

Fieldtype

The type of the source field. Possible values are:

• Coded
• Numeric
• Dated

Levels

The number of fields that are ANDed or Ored together to arrive at a complex constraint.

• For a simple constraint that involves only one source field’s data, Levels = 0.

Items

The number of items (defined as separate values, or separate ranges of values) that will be associated with the constraint.

• If the constraint field can have just two possible separate values (such as "Large" and "Medium" but not "Small"), then Items = 2.
• If the constraint field is a numeric range from 10 to 100, the value of Items is 1 (and not 90), because there is just one possible range of values.
• Each Items entry must have a VN entry in a Q-FieldN section associated with it. See below.

The [Q-FieldN] Sections

As well as the two key entries listed above, a Record Constraint section must also contain separate sections called "Q-FieldN" sections. 

• There is a Q-Field section for each FieldN entry.
• Q-FieldN sections define the specific constraint conditions that apply to each source field.

Each Q-FieldN section contains one entry, designated VN, for each possible value in a FieldN item. 

• The number of VN entries is the same as the value of "Items" shown above.
• VN specifies the exact condition that constrains the data in FieldN.

If FieldN is a Coded field, VN entries simply name the required value.

Example: V1=Small 

• In this example, the only possible value for V1 is Small. Data for Medium and Large is not included in the table or extract.

If FieldN is a Numeric field and the constraint uses a logical operator, VN states the condition for the value of VN.

Example: V1=">1000" 

• In this example, only values for V1 that are greater than 1000 are included.

If FieldN is a numeric field and the constraint is a band constraint, VN states the upper and lower bounds of the band.

Example: V1="[0,1000)" 

• In this example, only values for V1 that fall in a range from 0 [including 0] to 1000 [not including 1000] are included.

Note that in a range expression, a square bracket "[" or "]" means that the value inside it is inclusive of itself. A round bracket "(" or ")" means that the value inside it is not inclusive of itself. It is not unusual to use both kinds of bracket in the same expression to indicate that the higher and lower range boundaries do or do not include themselves.

Example of a record constraint

This RecordConstraint section of an IVU defines two constraints. 

• The first constraint, Field1, affects a source field called Quota whose format is Numeric. One item is associated with the constraint. The condition of the constraint is defined in [Q-Field1] as being a band constraint between the values of 0 (inclusive) and 20000 (not inclusive).

  The result of this record constraint is that only values of Quota between 0 and 20000 (but not including 20000) will be included in the table or extract that is being built.

• The second constraint, Field2, affects a source field called Province whose format is Coded. Two items, V1 and V2, are associated with the constraint. [Q-Field2] defines the coded value QUE as being the constraint on V1 and the coded value BC as being the constraint on V2.

  The result is that only data for Quebec and British Columbia will be included in the table or extract that is being created.

[RecordConstraint] Constraints=2 Field1=Quota,Numeric,0,1 Field2=Province,Coded,1,2
[Q-Field1] V1="[0,20000)"
[Q-Field2] V1=QUE V2=BC

IVU - Missing Values section

Missing values are values that do not appear in a table because they are unknown, unavailable, or confidential. The presence of a missing value in a cell is shown by a missing value indicator, and an explanation of the missing value is contained in a missing value description. (Missing values are similar to footnotes.)

If missing values are enabled, the MissingValues section of an IVU specifies the missing value indicators and definitions for the default table language. (Not used for creating extracts.)

The syntax to define a missing value is as follows:

Key=Indicator,Description

• Key is a numeric value from 1 to 16. The first key must be "1," subsequent keys must increment by 1, and there can be no interruptions in the numbering.
• A table can have up to 16 separate missing values.
• Key entries correspond to missing value definitions in the source definition file (IVS). See Source Definition File, Field Definitions section, in the Builder Help for more information.

The following describes the values in the MissingValues section of an IVU:

Examples

Example 1

The following MissingValues section of an IVU defines three missing values.

[MissingValues] 1=-,Missing Value 2=...,Secure Data 3=n/a,Data is not available

• Missing value 1 uses a dash (-) as a missing value indicator. The explanation is "Missing Value."
• Missing value 2 uses an ellipsis (…) as a missing value indicator. The explanation is "Secure Data."
• Missing value 3 uses the string "n/a" as a missing value indicator. The explanation is "Data is not available." 

Example 2: Missing Values in Multilingual Tables

If you are creating a multilingual table, your MissingValues section must include additional MissingValues subsections, one for each language used in the table. Each language-specific MissingValues subsection takes the following form:

[MissingValues-xxx]

"xxx" represents one of the 3-character Beyond 20/20 language codes – for example, [MissingValues-fra] for French-language missing values information or [MissingValues-deu] for German-language missing values information. See Language Codes in the Builder Help. 

The following language-specific MissingValues subsection defines French versions of the missing values shown in Example 1:

[MissingValues-fra] 1=-,Valeur Manquante 2=...,Valeur private 3=n/d,Le valeur n'est pas disponible

IVU - Footnotes section

This section enables you to specify text and background cell coloring for footnotes. It refers to the footnote table indices for each entry, starting with the value 1.

• If more than one language is defined in the table, additional sections of the form [Footnotes-xxx] are required, where xxx is one of the standard three-letter language codes that are defined by the Output section's LanguageN keys.
• The default [Footnotes] section always refers to the default language given by the Language1 key.

Each section will contain identical numeric keys denoting the footnote table indices for each entry. The minimum record will have two fields containing the reference mark and the description.

IVU - FootnoteLoader section

The footnote loader section contains the information specified in the Define Footnote Source dialog. 

• The footnote loader, formerly a separate application, has been incorporated into the Visual Builder as of version 7.1. To load footnotes using the Builder, point at the Source menu and click Define Footnote Source. See Define Footnote Source in the Visual Builder User Guide.

Footnote Loader Only definition

An IVU is a valid "footnote loader only IVU" if it contains a valid FootnoteLoader section and a Name key specifying an existing IVT file. There should be no Rules specified.

• When a valid "footnote loader only IVU" is loaded into the Builder, the Data Load Footnotes menu option will become available. If processed by the Integrators Builder, it will run only the Footnote Loader section of the Builder.