Beyond 20/20

TABLE OF CONTENTS

• Footnote Dictionary File
  • Footnote Dictionary File Fields
  • Rules for footnote loading and updating:
• Cell Position File
  • Cell Position File Fields
    • Date formats for time series dimensions
• Creating CSV input files (.CSV)
  • To create a Footnote Dictionary file:
  • To create a Footnote Cell Position file:

Loading footnotes into an IVT during the build requires two specialized source files for each table:

• Footnote Dictionary File - Contains the footnote text and footnote indicators, along with the footnote language.
• Cell Position File - contains information that enables the Builder to associate the footnotes defined in the footnote dictionary with the correct table cells.

The footnote dictionary and cell position files can be created either as tables in a Microsoft Access database (.MDB) or as comma-separated value files (CSV). You can choose either type to serve as input. 

• The footnote data can come from the same source file as the cell position data if you wish, or else they can be separate files. The two source files do not have to be of the same type. 

Note: Sample source files are attached.

Footnote Dictionary File

The Footnote Dictionary File contains the footnote text and footnote indicators, along with the footnote language. 

• The footnote dictionary file can be either an Access database (.MDB) or a comma-separated value file (.CSV).
• There must be one record for each footnote in each required language. For example, if you wanted to add three footnotes, one each in German, French, and English, your footnote dictionary file would contain nine records.

Note: Sample source files are provided in the folder \Documentation\Sample Data\Load Footnotes. 

Footnote Dictionary File Fields

Note: You can use any field names you wish, but if you want the fields to be recognized and loaded into the Define Footnote Source dialog box automatically, you should use the standard field names as shown below.

The following fields are required:

The following fields are optional:

Rules for footnote loading and updating:

1. FootnoteId must always contain sequential FootnoteId values starting at 1. These FootnoteId values are the only key values that will be used during loading.
2. If a Footnote Dictionary property (RefMark, Description, etc.) is modified and the dictionary is reloaded, the footnote dictionary entry in the table will be updated and all existing cell entries with the same FootnoteId will reflect the updated Footnote Dictionary entry.
3. The source file does not have to contain a definition for any FootnoteId/Language that is already defined in the IVT. This means that the source table may be empty so long as the cell position table only contains existing footnotes.
4. It is not possible to delete a Footnote Dictionary Entry once it has been added.

Notes:

• LangId: An index number for the language. Refers to the order in which the languages were added. For example, if English is the default language and French was added later, English has a LangId of 1 and French has a LangId of 2.

  To determine the index number of a language, open the table in Visual Builder and then open the Define Footnotes dialog box (Edit menu> Footnotes) or the Define Missing Values dialog box (Edit menu> Missing Values). The table's languages appear in the drop-down menu for Show Footnotes for Language or Show Missing Values for Language in the order in which they were added to the table. 

  In the following screenshot of a Define Footnotes dialog box, the language indexes are as follows:

  • English - 1
  • French - 2
    
• You can use a different reference mark for each language version of a footnote. For example, you could use an asterisk (*) for English-language footnotes and a dash (-) for German-language footnotes.
• If you need to use the same footnote for multiple cells, do not repeat the footnote information in the footnote dictionary. The footnote dictionary should contain only one entry per unique footnote.
• Start all Id fields with the digit 1.
• If you have two or more languages in your Beyond 20/20 table, you must have a record for each language in the footnote dictionary. If you do not have translated text for the secondary languages you can repeat the primary language text.
• Do not use unprintable characters such as tabs in the Text field. Tabs will cause the build to fail with an error. Other unprintable characters may not cause the build to fail, but they may cause the Beyond 20/20 Browser reading the output table to behave unpredictably.

Cell Position File

The Cell position file contains information that tells the Builder which footnotes will be associated with specific cells in the Beyond 20/20 table. 

Note: Sample source files are attached

Cell Position File Fields

The following fields are required in a cell position record.

Note: You can use any field names you wish, but if you want the fields to be recognized and loaded into the Load Footnotes dialog box automatically, you should use the standard field names as shown below.

Date formats for time series dimensions

If the dimension is a time series dimension, you must enter the date in the code field as a character string. Use one of the formats below, depending on the periodicity of the time series dimension.

Notes: 

• Using an incorrect date format will cause the build to fail.
• Dates must be in English, even if the table is in another language. For example, February must be Feb, not fév.

Notes:

• Do not omit any dimensions, even if they only contain one item.
• The design of the cell position table is dependent on the number of dimensions in the IVT. If the IVT has five dimensions, you will need Dim1 through Dim5 fields to describe the cell position for each footnote within a cell.
• FootnoteId corresponds to the FootnoteId field in the Footnote Dictionary file.
  • FootnoteID must be an integer. It cannot be a letter.
• Always use the Beyond 20/20 item code for a particular dimension item. For example, if Dim1 is Gender, Dim2 is the Age band, and Dim3 is Location, then each record in the cell position file could be created as follows (in tab delimited format):

  F     45-55    Ottawa     2

• Item codes must be less than 17 characters. This is a Beyond 20/20 system limit.

Creating CSV input files (.CSV)

Note: Sample source files are attached.

The easiest way to create csv files is to use a spreadsheet application such as Microsoft Excel. 

To create a Footnote Dictionary file:

1. Import the provided sample text file (ProductsFootnotes.csv) into an MS Excel spreadsheet. Delete the sample data.
2. Enter or import records for each footnote/language combination. Each column must be labelled in the header row.
3. When you have finished creating your footnote dictionary table, save the spreadsheet as a CSV file.

See attached file, ProductsFootnotes.csv

To create a Footnote Cell Position file:

1. Determine the dimension field order by opening your Beyond 20/20 input table (IVT) and selecting Summary from the File menu. The summary shows all the table's dimensions and their order. (For more information, see Dimension Order.)
2. Import the provided sample text file (ProductsPosition.csv) into an MS Excel spreadsheet. Delete the sample data.
3. Ensure that the cell position spreadsheet contains enough dimension columns to contain all the dimensions from the Beyond 20/20 table according to the IVT table summary (Dim1 through DimN).
4. For each cell requiring footnotes, and for each footnote to be assigned to that cell (up to 8 footnotes per cell), enter the appropriate dimension item values and corresponding footnote identifiers.
5. When you have finished creating your cell position file, save the spreadsheet as a CSV file.
6. Repeat the above procedures for each Beyond 20/20 table you wish to process.

See attached file, ProductsPosition.csv

See Also:

• How To Load Footnotes From External Files