Overview

An extract is an encoded and compressed form of a source file. It is distributed to Browser users so that they can quickly design and produce their own multi-dimensional tables without you, the Builder user, having to anticipate their needs.
In this section, we will explore the step-by-step procedures needed to create a Beyond 20/20 extract using a CSV file as our source data. Before you begin, unzip the files in the attached .zip file to a folder, Example2FilesToBuildExtractFromCSV.zip

Opening an CSV File

You can build a Beyond 20/20 extract from CSV source files.

This exercise shows you how to create an extract from an CSV source file. We will open a CSV source file, describe its source fields, and then create and document the extract.
When the source file is CSV, the Builder does not know the field types and treats all fields as text fields. You have to Set Field Information for each numeric or date column.

  • Before you begin, unzip the files in the attached .zip file to a folder, Example2FilesToBuildExtractFromCSV.zip

To open a CSV source file:

  1. Do one of the following:
    - Click the Open button on the toolbar.
    - From the File menu, choose Open.
    The     Open dialog box appears.

  2. In the Files of type area, select Comma Separated (*.csv).
  3. In the File name area, select 90GRDS.CSV
    Note: If you do not see 90GRDS.CSV listed, you can search for it in a different drive and directory.  
  4. Click Open.
    An untitled, empty table view appears. 

     Now you can set the field information for those source fields that are not character or strings.

Setting Field Information

Use to define the treatment of data in a table, and set missing value field information.

  • A writable table must be open and one or more fields must have been defined.

To open:

  • From the Source menu, choose Set Field Information.
  • Press Alt, S, N.

Options:

Source Fields

Displays all the field definitions that are in use in the table.

Select All

Selects all the field definitions.

Select None 

Clears all the selected field definitions. 

Name

The name of the source field. Click Name to sort the source fields alphabetically by their names.

Type

Shows the type of data that is contained in the source field. Possibilities are Date, Numeric, and Character. Click Type to sort the source fields alphabetically by their types.

Treatment

The treatment of the data in a source field. Click Treatment to sort the source fields alphabetically by their treatments. Default treatments are: 

  • Dated for Date type information
  • Numeric for Numeric type information
  • Coded for Character type information. 

Start

The starting position of the field information in the source file. Click Start to sort the source fields numerically by their start positions.

Width

The width of the field information in number of characters. For CSV the width is always 1. Click Width to sort the source fields numerically by their widths.

Treatment

Select Coded, Numeric, Dated, or Ignore to force a source field to use a particular treatment. 

Apply

Applies the selected treatment to a source field. 

Missing Values

Displays all the missing values that have been defined for the selected fields. 

Note: A missing value that applies to one or more of the selected fields but not to all of them displays in normal text, and a missing value that applies to all the selected fields displays in bold text. 

Source File Code

Displays the codes for the source file missing values that have been defined.

Missing Type

Displays the missing value types for the defined missing values.

Add

Opens the Add Source File Missing Value dialog box, which enables you to add a new missing value or edit an existing one. 

Remove

Deletes the selected missing value from the source information.


To Set the Field Information for the fields in your CSV file:


  1. From the Source menu, choose Set Field Information.
    The Set Field Information dialog appears:

  2. Use the <Ctrl> key on your keyboard to select Salary-1st Job, Salary-2nd Job and Weight and then select Numeric and then click Apply.
    You will be prompted to confirm that you want to apply this to all 3 fields:

    Click Yes

    The 3 fields change to Numeric type in the list:
  3. Click Hire Date and then select Dated and then click Apply
    Hire Date changes to Dated type in the list

Defining Missing Values

When a source file has numeric fields that contain codes that represent data that is confidential or unavailable, you can identify those codes to the Builder as missing values.
Missing values are represented in the Builder and Browser by a missing value indicator in a table cell.
When you hover your mouse over a cell that contains a missing value indicator, the missing value text
appears as a popup.
Before you can add missing values you must know the following information about the source file:

  • Which numeric source fields contain missing values?
    In our example CSV file, the source fields Salary-1st Job and Salary-2nd Job have missing
    values.
  • What characters or symbols are used in the source file to indicate the missing values?
    In our example CSV file, blanks represent missing values.


Note: If there are any blanks or non numeric values in a numeric field, missing values must be defined for them before the extract can be created.

To assign the missing values to source fields:

  1. In the Set Field Information box, use the <Ctrl> key on your keyboard to select the source fields that contains missing values, Salary-1st Job, and Salary-2nd Job and click Add.
    A popup message appears telling you that missing values are not currently enabled.
    This message relates to creating IVT files. We can ignore it.
  2. Click OK.
    The Add Source File Missing Value dialog box appears. It includes a default missing value.
  3. Leave Source File Code blank because the Salary fields in the example CSV file contain blanks and click OK. 
  4. Click OK in the Set Field Information dialog
    Your Builder screen should now look like this:
     
     You are now ready to provide more information for your Time dimension

How To Define the Periodicity of a Time Series dimension

When you define a source field as a Dated field, this means that you intend this source field to become a Time series dimension. When you do this you need to provide Time series information for the source field indicating the date format and whether it is monthly, annual, etc.

To provide this information, right click on the source field and select Source Field Summary.
The Source Field Summary box appears:

Source Field Summary View

Displays descriptive information about the active source field.

To open:

  • From the Data menu, choose Source Field Summary.
  • Click the right mouse button on a source field and choose Source Field Summary.
  • Press Alt, T, M.

To Set the Field Information for the Hire Date field in your CSV file:

  1. Right click source field, Hire Date, and select Source Field Summary.
    The Source Field Summary dialog appears:
  2. Enter information for the field.
    Enter a Field Description
  3. Category is used to help the extract user find a source field
    Enter a Category
  4. Make sure Date Field is selected under Treatment and select Monthly for the Data Frequency.
  5. Select yyyy/m/d from the Date Format list.
  6. Click OK


Now you can save the source definition file (IVS).

Saving the Source Definition File

You can load a saved source definition file whenever you want to create a table or an extract from the

same source file. We will save the source definition file as 90GRDS.IVS.

To save the source definition file:

  1. In the File menu, click Save As.
    The Save As dialog box appears. In Save as type, choose Source Defn (*.ivs).
  2. In the File name area, enter 90GRDS. Choose a location and folder to save the file, and then click

    Save.

    The source file attributes are recorded and saved in the source definition file, 90GRDS.IVS.


Setting a Weight Field

The weighting factor of a record is the number of times that the record is logically replicated when the
table or extract is built.

  • Weighting is used to extrapolate from survey data to an entire population.
  • Only one field can be set as a weight field. It must be a numeric field.

When a weight is set before an extract is created, any tables built from the extract will contain weighted
results. The Browser user always has the option of applying a different weight field or removing any
weight field association before building a table from an extract.

To set a weight field:

  1. From the Data menu, choose Set Weight Field.
    The     Set Weight Field dialog box appears. Only numeric fields from the source file are shown.

  2. Select the checkbox for Use weight field.
  3. Select Weight to use as the weight field, and click OK.
    The source field selected to weight the data (Weight) appears dimmed in the source field bar to show that it is unavailable for use as a dimension.

Defining Labels

When you build a table or an extract, each dimension or source field is filled with coded items. To provide Browser users with the information they need to understand the items, you can also add labels.

Labels are an alternate set of descriptions for coded items. Usually they are more user-friendly and easier to understand than codes.

  • Labels can be entered manually or imported from a comma-separated value file (.CSV), a dimension definition file (IVD), or an ASCII text file.
  • For details on creating a comma-separated value file to use as labels, see Adding Labels, How To Add Labels To A Dimension or Source Field

Sample .CSV files are included in the the attached file, Example2FilesToBuildExtractFromCSV.zip. We will import labels from CSV files into the Birth State and Job Code fields of our source file.

To import a comma-separated value file:

  1. Right-click on the Birth State source field tile and select Source Field Summary.
    The Source Field Summary dialog box appears.
  2. Click the browse button at the end of the Definition File area.

    The Select the Dimension Definition File dialog box appears.


  3. In Files of type, select Comma Separated (*.csv).

  4. In File name, select state.csv (located in the zip file, Example2FilesToBuildExtractFromCSV.zip) and click Open.

  5. In the Source Field Summary dialog box, click OK.

Repeat the same procedure to import the labels for the Job Code dimensions, as follows:

  1.  Right-click on the Job Code-1st Job source field tile and select Source Field Summary.
    The Source Field Summary dialog box appears.
  2. Click the browse button at the end of Definition File.
  3. In the Files of type area, select Comma Separated (*.csv).
  4. In the File name area, select job.csv (located in the zip file, Example2FilesToBuildExtractFromCSV.zip) and click Open.
  5. In the Source Field Summary dialog box, click OK.

Repeat for Job Code-2nd Job

Creating the Extract

Once the source file has been opened and defined, you can proceed to create an extract.

To create an extract:

  1. From the Data menu, choose Create Extract.
    The Save As dialog box appears.
  2. In File name, enter 90GR as the file name for the extract. If necessary, select a different drive

    or directory.

  3. In Save as type, select Extract Files (*.ivx).

  4. Click Save.

  5. You are prompted to save the Update Definition. Choose Yes.

    A Save As dialog box appears.

  6. In the Save As dialog box, enter a name and location for the Update Definition File (IVU) and click

    Save.

  7. A message asks if you want to create the extract now. Click Yes.

    An extract file (IVX) is created, and a dimension definition file (IVD) is generated for each source

    field.