In this article we will look into loading the User-Defined Table (UDT) using the HDL FF by reading a pipe delimited flat file.
If you are new to User-Defined Table, please check out this article
If you are new to HDL Transformation Formula, please go through the below posts to understand better
Once you have the idea on HDL FF and UDT, you can read through the below content to know how to create HDL FF to load the UDT using HDL.
We can load data into the UDT using HDL by using the Business Object UserDefinedTable.dat. This business object has 4 sections, one for creating UDT Properties, UDT Rows, UDT Columns and UDT Column Instances.
If we have a requirement to load data into the UDT on a regular basis on some schedule, we can create the HDL FF to load the Rows and Column Instances using the HDL FF. The basic structure of UDT which includes the UDT Properties and UDT Columns should be created first (either manually or using HDL file) which is a one time task.
Once the structure of UDT is ready, then we can load the rows and column instances for those rows on an on-going basis.
Table of Contents
Sample Structure of UDT :
UDT Name – XXHR_UDT
Row – EMPLID
Columns – NAME, COMPANY and SSN
Sample Flat file:
Employee Number|Name|Company|NID 324334234|Test, Employee 1|1234|123456789 234324343|Test, Employee 2||234567809 324324343|Test, Employee 3|1234|432434345
HDL Transformation Formula to convert Flat file to HDL File
/* HDL Transformation Formula to load data into User-Defined Table from a pipe delimited flat file */
/* Author: Sricharan Monigari */
/* Inputs */
INPUTS ARE OPERATION (text),
LINENO (number), LINEREPEATNO (number), POSITION1 (text), POSITION2 (text), POSITION3 (text), POSITION4 (text), POSITION5 (text)
DEFAULT FOR LINENO IS 1
DEFAULT FOR POSITION1 IS 'NO DATA'
DEFAULT FOR POSITION2 IS 'NO DATA'
DEFAULT FOR POSITION3 IS 'NO DATA'
DEFAULT FOR POSITION4 IS 'NO DATA'
DEFAULT FOR LINEREPEATNO IS 1
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE IS ' '
/* Calculations */
IF OPERATION='FILETYPE' THEN
OUTPUTVALUE='DELIMITED'
ELSE IF OPERATION='DELIMITER' THEN
OUTPUTVALUE='|'
ELSE IF OPERATION='READ' THEN
OUTPUTVALUE='NONE'
ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN(
OUTPUTVALUE = '1'
RETURN OUTPUTVALUE
)
ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN
(
METADATA1[1] = 'UserDefinedTable' /*FileName*/ /*Reserved*/
METADATA1[2] = 'UserDefinedTableRow' /*FileDiscriminator*/ /*Reserved*/
METADATA1[3] = 'EffectiveEndDate'
METADATA1[4] = 'EffectiveStartDate'
METADATA1[5] = 'RowLowRangeOrName'
METADATA1[6] = 'UserTableCode'
METADATA1[7] = 'LegislativeDataGroupName'
METADATA1[8] = 'RowName'
METADATA2[1] = 'UserDefinedTable' /*FileName*/ /*Reserved*/
METADATA2[2] = 'UserDefinedTableColumnInstance' /*FileDiscriminator*/ /*Reserved*/
METADATA2[3] = 'EffectiveEndDate'
METADATA2[4] = 'EffectiveStartDate'
METADATA2[5] = 'Value'
METADATA2[6] = 'LegislativeDataGroupName'
METADATA2[7] = 'UserColumnCode'
METADATA2[8] = 'RowLowRangeOrName'
METADATA2[9] = 'UserTableCode'
RETURN METADATA1, METADATA2 /*Only two as Return value for NUMBEROFBUSINESSOBJECTS is 2*/
)
ELSE IF OPERATION='MAP' THEN
(
FileName = 'UserDefinedTable'
BusinessOperation = 'MERGE'
UserTableCode = 'XXHR_UDT'
EffectiveStartDate = '2000/01/01'
EffectiveEndDate = '4712/12/31'
LegislativeDataGroupName = 'US LDG'
TODAYS_DATE = TO_CHAR(TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE, 'YYYY/MM/DD'), 'YYYYMMDD')
RowLowRangeOrName = trim(replace(POSITION1,'"',' ')) || '-' || TODAYS_DATE
FIRST_COLUMN = trim(replace(POSITION1,'"',' '))
RowName = trim(replace(POSITION1,'"',' ')) || '-' || TODAYS_DATE
IF LINEREPEATNO=1 THEN
(
IF FIRST_COLUMN = 'Employee Number' OR FIRST_COLUMN = 'Employee_Number' THEN
(LINEREPEAT = 'N'
/*dummy = ESS_LOG_WRITE('Skipping Header Row')*/
RETURN LINEREPEAT) /* To skip header row */
ELSE
(
FileDiscriminator='UserDefinedTableRow'
LINEREPEAT = 'Y'
/*dummy = ESS_LOG_WRITE('FileDiscriminator: '||FileDiscriminator)
dummy = ESS_LOG_WRITE('LINEREPEAT: '||LINEREPEAT)*/
RETURN BusinessOperation,FileName,FileDiscriminator,EffectiveEndDate,EffectiveStartDate,RowLowRangeOrName,UserTableCode,LegislativeDataGroupName,RowName,LINEREPEAT,LINEREPEATNO
)
)
ELSE
(
IF LINEREPEATNO>1 and LINEREPEATNO < 5 THEN
(
If LINEREPEATNO = 4 then
(LINEREPEAT = 'N')
else
(LINEREPEAT = 'Y')
FileDiscriminator='UserDefinedTableColumnInstance'
If LINEREPEATNO = 2 AND POSITION2 <> 'NO DATA' THEN
(UserColumnCode = 'NAME'
Value = trim(replace(POSITION2,'"',' ')))
else if LINEREPEATNO = 3 AND POSITION3 <> 'NO DATA' THEN
(UserColumnCode = 'COMPANY'
Value = trim(replace(POSITION3,'"',' ')))
else if LINEREPEATNO = 4 AND POSITION4 <> 'NO DATA' THEN
(UserColumnCode = 'SSN'
Value = trim(replace(POSITION4,'"',' ')))
else IF LINEREPEATNO < 5 THEN
(RETURN LINEREPEAT)
/*dummy = ESS_LOG_WRITE('FileDiscriminator: ' || FileDiscriminator || ' - ' || 'UserColumnCode: '||UserColumnCode || ' - ' || 'Value: '|| Value)*/
if isnull(Value) = 'N' then
(
If LINEREPEATNO = 4 then
(LINEREPEAT = 'N')
else
(LINEREPEAT = 'Y')
RETURN LINEREPEAT
)
RETURN BusinessOperation,FileName,FileDiscriminator,EffectiveEndDate,EffectiveStartDate,Value,LegislativeDataGroupName,UserColumnCode,RowLowRangeOrName,UserTableCode,LINEREPEAT,LINEREPEATNO
)
)
)
ELSE
OUTPUTVALUE='NONE'
RETURN OUTPUTVALUE
/* End Formula Text */This HDL FF will read the input file and convert it to HDL file loading the rows and column instance values for those corresponding rows. I have added logic to add the system date concatenated to the row value as those row values might get repeated again. In the query we have to handle that date by trimming it.
The HDL FF will skip the record if it encounters the Header record
The HDL FF will loop through multiple times using the LINEREPEAT flag till all the columns are read through.
The HDL FF will not create a column instance row if the input file doesn’t have value for a column.
Sample Output HDL file that gets created with this FF:
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|RowHighRange|RowLowRangeOrName|UserTableCode|LegislativeDataGroupName|RowName MERGE|UserDefinedTableRow||4712/12/31|2000/01/01||324334234-20200827|XXHR_UDT|US LDG|324334234-20200827 MERGE|UserDefinedTableRow||4712/12/31|2000/01/01||234324343-20200827|XXHR_UDT|US LDG|234324343-20200827 MERGE|UserDefinedTableRow||4712/12/31|2000/01/01||324324343-20200827|XXHR_UDT|US LDG|324324343-20200827 METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|RowLowRangeOrName|UserTableCode MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|Test, Employee 1|US LDG|NAME|324334234-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|1234|US LDG|COMPANY|324334234-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|123456789|US LDG|SSN|324334234-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|Test, Employee 2|US LDG|NAME|234324343-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|234567809|US LDG|SSN|234324343-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|Test, Employee 3|US LDG|NAME|324324343-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|1234|US LDG|COMPANY|324324343-20200827|XXHR_UDT MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|432434345|US LDG|SSN|324324343-20200827|XXHR_UDT
We can see that DAT file has been generated to create the rows and column instances for the input data.
Hope this helps when you have similar requirement on Uuser-Defined Tables. Feel free to modify it as necessary.
