collate

Edit online

The collate function is used to construct a hierarchical relationship between two groups that are not currently related hierarchically but which are instead related by “foreign key” fields. One of the groups is considered the “master” group and the other is considered the “detail” group. Instances of the detail group are matched against the master group and moved to a “result” group which must be a child of the master group. The result group must have an identical field structure to the detail group. The master and detail groups must both be sorted into the same order using the sort function prior to performing the collation. After the collate function is performed, the data in the detail group is no longer available because it has been moved to the result group.

Note: This is an advanced function which requires understanding of how field data is accessed in translator storage and how indices are used to reference specific instances of groups.

In the command syntax, “ordering” is either the keyword ASC or DESC to indicate that the groups are sorted in ascending or descending order, respectively. If no ordering is given for a particular field, ascending order is assumed. Any number of fields may be specified, but the number of master and detail fields specified must match.

Note: If the collated data is too large, you may receive an out of memory error.

In this example, you need to process a flat file that contains information for Stores and Sales. All of the Stores information is listed first in the file, in no particular order, and all of the Sales information follows the Stores information, in no particular order. You need to sort the information and associate the Sales information to the appropriate Store information. The Stores and Sales records contain a common field: Stor_id.

Syntax

Use this syntax:


collate (MASTERGROUPNAME[iteration], MASTERFIELDNAME ordering, 
MASTERFIELDNAME ordering, …, DETAILGROUPNAME[iteration], DETAILFIELDNAME, 
DETAILFIELDNAME, …, RESULTGROUPNAME))

Example

An example of this function follows:


collate($POHeader, #PONumber, $PODetail, #POHeaderNumber, $PODetailResult);

Map Layout

The input side of the map contains two repeating groups that are children to the input level: Stores_Group and Sales_Group. The Stores_Group group contains a single occurring record for the Stores records in the input file, and the Sales_Group group contains a single occurring record for the Sales records in the input file.

The Stores_Group is the Master group and the Sales_Group is the Detail Group when you write the Collate extended rule. Since the Stores_Group is the master group, the result group must be contained within this group.

Therefore, the Stores_Group structure contains the single occurring record for the Stores records in the input file and a repeating Result_Group. The Result_Group contains a temporary Sales record that is an exact copy of the Sales_Record in the Sales_Group.

Collate Input On-End Extended Rule

To sort and collate the data, place the following rule on the Input On-End Extended Rules:


SORT($Stores_Group,#Stores_Stor_ID ASC);
SORT($Sales_Group,#Sales_Stor_ID ASC);
 
COLLATE ($Stores_Group, #Stores_Stor_ID ASC,$Sales_Group, 
	#Sales_Stor_ID,$ Result_Group);

Sample Input File

The following is the sample input file:


STORES8042   Bookbeat             679 Carson St.       Portland   OR   890762
STORES7066   Barnum's             567 Pasadena Ave.    Tustin     CA   927892
STORES7896   Fricative Bookshop   89 Madison St.       Fremont    CA   900192    
SALES7896QQ2299              1993-10-28 00:00:00      15     Net 60      BU78322    
SALES8042423LL922            1994-09-14 00:00:00      15     ON invoice  MC30212    
SALES8042P723                1993-03-11 00:00:00      25     Net 30      BU11112    
SALES7066QA7442.3            1994-09-13 00:00:00      75     ON invoice  PS20912    
SALES7896TQ456               1993-12-12 00:00:00      10     Net 60      MC22222    
SALES7896X999                1993-02-21 00:00:00      35     ON invoice  BU20752    
SALES8042423LL930            1994-09-14 00:00:00      10     ON invoice  BU10322    
SALES8042QA879.1             1993-05-22 00:00:00      30     Net 30      PC10352    
SALES7066A2976               1993-05-24 00:00:00      50     Net 30      PC88882

Output File

The following is an example of the output file after translation:


STORES7066   Barnum's             567 Pasadena Ave.    Tustin     CA   927892
SALES7066QA7442.3            1994-09-13 00:00:00      75     ON invoice  PS20912    
SALES7066A2976               1993-05-24 00:00:00      50     Net 30      PC88882    
STORES7896   Fricative Bookshop   89 Madison St.       Fremont    CA   900192    
SALES7896QQ2299              1993-10-28 00:00:00      15     Net 60      BU78322    
SALES7896TQ456               1993-12-12 00:00:00      10     Net 60      MC22222    
SALES7896X999                1993-02-21 00:00:00      35     ON invoice  BU20752    
STORES8042   Bookbeat             679 Carson St.       Portland   OR   890762
SALES8042423LL922            1994-09-14 00:00:00      15     ON invoice  MC30212    
SALES8042P723                1993-03-11 00:00:00      25     Net 30      BU11112    
SALES8042423LL930            1994-09-14 00:00:00      10     ON invoice  BU10322    
SALES8042QA879.1             1993-05-22 00:00:00      30     Net 30      PC10352