In order for the system to identify columns in the original source, they have to be mapped to the corresponding columns of the Source Layout. Original Data Configuration tab (Fig.) includes:
1. Source Column column on the left that is filled up with the column names as they were created during the Source Layout setup.
2. Original Column column needs to be populated with names of the fields of the actual source table.
If the original source is a file with columns delimited by tabs or other characters, the Original Column fields will be populated with numbers in order of which the columns appear in the source table.
If the original data arrives in the table format, the Original column will be populated with original data table column names that must correspond to the names of columns as they are set up in the Layout.
In the case of a new source, the Original Column column comes up empty and needs to be filled manually.
button fills up the Original Column column with the default configuration that is determined by the incoming data format. You can do it either in one shot (with no entries in the Source Columns column selected) or you can do it one column at a time by highlighting the entry and pressing Populate Default Configuration button.
If the names of the columns in the layout differ from the original table, usebuttons to select appropriate column names.
button removes the Original Column value from the selected rows of the tab’s grid.
3. Column Format. This part of the data grid serves a dual purpose:
- You can modify the Data Display format for Dates and Numbers overriding default specified on the Layout. The changes you make here will be user-specific.
The data format representing the Date field should be described using the following attributes:
YYYY – 4-character Year, i.e. 2007
YY – 2-character year, i.e. 96
MM – month number, i.e. 11
MON – month name, i.e. JUN
DD – day, i.e. 30
HH24 – hours in 24-hour notation, i.e. 18 for 6 pm
MI – minutes i.e. 45
SS – seconds i.e. 22
If the original data looks like:
- ‘25-jan-2007’, the format which we need to use will be: ‘DD-MON-YYYY’
- 20070125 16:02:33’, the format will be: ‘YYYYMMDD HH24:MI:SS’
- ‘20070125 16:02:33.123’, the format will be: ‘YYYYMMDD HH24:MI:SS.FF’
Note: Data Display Format entered on Original Data Configuration tab will override the default Data Display Format entered in the Layout Setup.
- You can reformat the column’s data by inserting a database-specific function, which will be added to the loader program. The expression below, for example, allows the system operating in Oracle environment to accept numbers with decimal commas:
“TO_NUMBER(:gross_amount,’999999999999D9999999999999999999999999′,’NLS_NUMERIC_CHARACTERS = ”,.”’)”
Note: If you are not clear on how to do any of this, consult your IT or call Axiom SL support.