Blank Values in Hold Files

Hold files generated from match requests can contain blank values as the result of a one-to-many relationship between the data files.  For instance, consider the following hold file generated by a match between the Active Current Status (AC) file and the Payment History (PH) file.  

 

SSA          AC:WNAME     PH:PAYPERIOD   PH:GROSSPAY

---          --------      -----------   -----------

001-11-1111  RHINO, LEE   2004/07          $2,500.00

002-22-2222  MONKEY, PAT  2004/05          $3,200.00

002-22-2222               2004/06          $3,200.00

002-22-2222               2004/07          $3,200.00

003-33-3333  BEAR, LOREN  2004/07          $1,327.20

 

The AC file has only one value for AC:WNAME per SSA.  However, the PH file can have many values for PH:PAYPERIOD and PH:GROSSPAY per SSA.  As a result of the one-to-many relationship, the hold file has blank values for AC:WNAME.  To 'fill-in' the missing values, create a defined field.  For example:

 

EMPLOYEE/A39 =

   IF (AC:WNAME EQ ' ') AND (SSA EQ LAST SSA) AND

      (LAST AC:WNAME NE ' ') THEN LAST AC:WNAME

   ELSE

   IF (AC:WNAME EQ ' ') AND (SSA EQ LAST SSA) AND

      (LAST AC:WNAME EQ ' ') THEN LAST EMPLOYEE

   ELSE AC:WNAME;

 

The defined field EMPLOYEE has a value for each SSA so it would be used instead of AC:WNAME to generate the final output.

 

SSA          AC:WNAME     EMPLOYEE    PH:PAYPERIOD  PH:GROSSPAY

---          --------     --------    ------------  -----------

001-11-1111  RHINO, LEE   RHINO, LEE       2004/07    $2,500.00

002-22-2222  MONKEY, PAT  MONKEY,PAT       2004/05    $3,200.00

002-22-2222               MONKEY,PAT       2004/06    $3,200.00

002-22-2222               MONKEY,PAT       2004/07    $3,200.00

003-33-3333  BEAR, LOREN  BEAR, LOREN      2004/07    $1,327.20​



Last Updated: February 28, 2024