This page (revision-10) was last changed on 26-Nov-2021 10:22 by RForbes

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
10 26-Nov-2021 10:22 10 KB RForbes to previous LMTD INTERFACE FORMAT ==> DATA LOADER IDIF FORMAT
9 26-Nov-2021 10:22 10 KB RForbes to previous | to last Updates based on experiences
8 26-Nov-2021 10:22 9 KB RForbes to previous | to last
7 26-Nov-2021 10:22 9 KB RForbes to previous | to last
6 26-Nov-2021 10:22 9 KB JAiken to previous | to last
5 26-Nov-2021 10:22 9 KB JEscott to previous | to last
4 26-Nov-2021 10:22 9 KB JEscott to previous | to last
3 26-Nov-2021 10:22 8 KB JEscott to previous | to last
2 26-Nov-2021 10:22 8 KB JEscott to previous | to last
1 26-Nov-2021 10:22 2 KB JEscott to last

Page References

Incoming links Outgoing links
DATA LOADER IDIF FORMAT
DATA LOADER IDIF FORMAT

Version management

Difference between version and

At line 1 changed one line
The following reviews what should be defined in [IDIF] when creating an interface format to be used to load an external file using LMTD.
The following reviews what should be defined in [IDIF] when creating an interface format to be used to load an external file using the deta loader mechanism. data Loader functions start with the letter L as in [LMTD],[LEVS],[LBEN],]LPTS] etc.
At line 3 changed one line
Unless explicitly noted, IDIF fields are not used/referenced by the LMTD based functions ([LBEN], [LPTS], etc)
Unless explicitly noted, IDIF fields are not used/referenced by the data loader functions.
At line 10 removed one line
;[File Name|FILE_NAME]:This field specifies the default file name.
At line 19 changed one line
;[Derivation Expression|DERIVATION_EXPRESSION]: Derivation expressions may be used to reference the current field or other fields on the same record. Any [PL/SQL internal function|CALLABLE FUNCTION] or data base function may be used.
;[Derivation Expression|DERIVATION_EXPRESSION]: Derivation expressions may be used to reference the current field or other fields on the same record. Any [PL/SQL built-in function or data base function|CALLABLE FUNCTION] may be used.
;[Comments|COMMENTS]:While this field is not used in the processing of the interface, it is highly recommended that this be used to describe the field and any derivation expressions you may have in it.
At line 22 changed one line
!Derivation Logic with LMTD
!Record Skipping
At line 25 changed one line
In a fixed-position [IDIF], we can add virtual columns, by setting the Beg Posn and End Posn values to '-1'. Virtual columns could be columns that are required fields for the table but that are not included in the source file or they could be used to help fill a unique key set.
!Qualification
If you have defined a field as a Qualification record type, this field is used to determine if the record should be processed or not. The __Constant Value__ field will contain the value that the field must equal for it to be able to proceed with loading this record. If the field resolves to a different value, the record will be bypassed. You may use derivation expressions to evaluate field values as long as the end result matches the value in the constant value, the record will be passed. Normally the fields that are marked for qualification are noted as Foreign Columns (i.e. BEL.F_QUAL1) as described below.
At line 27 changed one line
In a comma delimited [IDIF], we can add virtual columns by setting the Beg Posn and End Posn values to '0'. These virtual columns may be used as helper fields to focus the LMTD to the correct record to update or they may be used to populate fields in a table that are not provided in the file.
By default (if Record Type is not set) all records are qualified.
At line 29 changed one line
If there are virtual columns defined using either of the above set up and there is no value defined in the file there must be some sort of stub value defined in the Constant field or Derivation Expression.
!Virtual Columns
Virtual columns could be columns that are required fields for the table but that are not included in the source file (may be derived or set to constant values as a result) or they could be used to help fill a unique key identifier. They may also be used for qualification of the records, as needed.
At line 31 changed one line
The 'Constant Value' attribute allows you to load a constant value for a specified field. This value will override any value mapped to the field in the source file. You could enter a space in the Constant field to represent a null value.
In a fixed-position [IDIF], we can add virtual columns, by setting the Beg Posn and End Posn values to '-1'.
At line 33 changed one line
Virtual columns can also be added to a comma delimited format by specifying -1 in the Beg Posn and End Posn. However, when using this type of virtual column in a comma delimited format, there must be a stub value provided in the file.
In a delimited [IDIF], we can add virtual columns by setting the Beg Posn and End Posn values to '0'. Virtual columns can also be added to a comma delimited format by specifying -1 in the Beg Posn and End Posn. However, when using this type of virtual column in a comma delimited format, there must be a stub value provided in the file.
At line 37 added 5 lines
If there are virtual columns defined using either of the above set up and there is no value defined in the file there must be some sort of stub value defined in the Constant field or Derivation Expression.
!Constant Values
The 'Constant Value' attribute allows you to load a constant value for a specified field. This value will override any value mapped to the field in the source file. You could enter a space in the Constant field to represent a blank value. or the word NULL in the derivation expression field to represent a NULL value.
At line 38 changed 2 lines
In the source file, we must have a "stub" value representing the field value for
EID.GOVT_CODE_VERIFIED. This could look something like: 12345, or 12346,N or 12347,Y or , ,
In the source file, we must have a "stub" value representing the field value for EID.GOVT_CODE_VERIFIED. This could look something like: 12345, or 12346,N or 12347,Y or , ,
At line 45 changed one line
Column names do not need to exist in the table and can be used to represent field names/values from a non-[{$applicationname}] database. The values for these non-[{$applicationname}] fields can be used to derive the values for [{$applicationname}] columns before inserting/updating. The naming convention for non-[{$applicationname}] columns is to prefix the column names with "F_". An example is PSL.F_TRANSACTION_CODE, where PSL determines the base table to load into (Sundries), F_ indicates it is a foreign column, and Transaction_Code is the foreign column name.
!Foreign Columns
Column names do not need to exist in the table and can be used to represent field names/values from a non-Personality database. The values for these non-Personality fields can be used to derive the values for Personality columns before inserting/updating. The naming convention for non-Personality columns is to prefix the column names with "F_". An example is PSL.F_TRANSACTION_CODE, where PSL determines the base table to load into (Sundries), F_ indicates it is a foreign column, and Transaction_Code is the foreign column name.
At line 54 added 3 lines
Fields that start with F_ are not loaded into te data base, but may be used for fillers, qualifiers or for other derivation expression usage.
!User Defined Fields
At line 49 changed one line
To load a constant value of NULL, the user will need to set a string value of ' ' (2 consecutive single quotes) in the constant value field. This value avoids ambiguity between a string value of "NULL" and an actual NULL reference.
!NULL Values
To load a constant value of NULL, the user will need to set a string value of ' ' (2 consecutive single quotes) in the constant value field or a derivation expression value of NULL. This value avoids ambiguity between a string value of "NULL" and an actual NULL reference.
At line 62 added 21 lines
!Derivation Expressions
Value derivation or manipulation is respected by the LMTD using the [IDIF] screen. You populate the 'Derivation Expression' field on the [IDIF] screen for the field needed to manipulate or derive.
The derivation expression must result in a character value returned from any or all function calls. If you wish to use the current field as a parameter in any function call, then you would indicate that field with a tilde (~). You may reference other field values on the same record by naming the field exactly as it is typed in the FIELD NAME field, __preceded by a colon (:)__. You may reference multiple variables in the same derivation expression.
For example, we can invoke a built-in function like [lpad|LPAD and RPAD] to add zeroes in front of an employee number on the field that would load it:
{{{
LPAD(~,'0',6)
}}}
or we could reference the same input field in a different field as:
{{{
LPAD(:BEL.EEM_ID.EID_ID.PERSON_CODE,'0',6)
}}}
Note that a reference to another field will give you the field value as it is on the file, not the final value
!Business Logic
At line 64 changed one line
When defining the interface it is very important that the correct data is provided for the column names. It is also important to define the correct Unique Keys (UKs) for a particular table so the system can correctly focus on the right record to update/insert. You can override with more UKs columns but if not all of the default ones are provided, the system will assume the values for the ones not provided are null.
!Unique Keys
At line 98 added 2 lines
The data loader can determine the linkages required for the records being loaded based upon unique key values. When defining the interface it is very important that the correct data is provided for the column names. It is also important to define the correct Unique Keys (UKs) for a particular table so the system can correctly focus on the right record to update/insert. You can override with more UKs columns but if not all of the default ones are provided, the system will assume the values for the ones not provided are null.
At line 68 changed 2 lines
An example of where this would be used is for loading into the Sundries table as there is no defined UK column set for that table. Fields which could be used to define a UK set for this table could be, PSL.START_DATE, PSL.PPC_ID.PC_CODE, PSLD.PPC_ID.PC_CODE,
PSL.EEM_ID.EID_ID.PERSON_CODE.
An example of where this would be used is for loading into the Sundries table as there is no defined UK column set for that table. Fields which could be used to define a UK set for this table could be, PSL.START_DATE, PSL.PPC_ID.PC_CODE, PSLD.PPC_ID.PC_CODE, PSL.EEM_ID.EID_ID.PERSON_CODE.
At line 71 removed one line
You can now manually define record qualification using the [IDIF]. To do so, you must set the Record Type of the interface field to 'Qualify Record' and define a qualifying value for the field in the Constant Value field.
At line 73 removed 13 lines
If the field value (before the application of derivation expression, if one exists) matches the value defined in the Constant Value field, then the record is qualified and permitted to be loaded. If the values do not match, the entire record is skipped. By default (if Record Type is not set) all records are qualified.
You can use an existing field to use as a record qualifier or you may create a foreign field to use a qualifier and reference another field using a derivation expression. The above example uses a foreign field as a qualifier record. In the example, if value in the time entered field in the file does not equal 00000000 then the value passes the case when scenario (true) and the
record qualifies. If the value did equal 0000000 then the value would fail the case when clause (false) and the record would be bypassed.
To load derived values for columns, the 'Beg Posn' and 'End Posn' values for the interface field must be set to '-1'.
Value derivation or manipulation is respected by the LMTD using the [IDIF] screen. You populate the 'Derivation Expression' field on the [IDIF] screen for the field needed to manipulate or derive.
[{If var='loginstatus' contains 'authenticated'
At line 87 changed 4 lines
![Discussion|Edit:Internal.LMTD+INTERFACE+FORMAT]
[{InsertPage page='Internal.LMTD+INTERFACE+FORMAT' default='Click to create a new discussion page'}]
}]
![Notes|Edit:Internal.LMTD+INTERFACE+FORMAT]
[{InsertPage page='Internal.LMTD+INTERFACE+FORMAT' default='Click to create a new notes page'}]
This page (revision-10) was last changed on 26-Nov-2021 10:22 by RForbesTop