The following document explains (hopefully) how indexing works.
The input file is a flat-file and we want to group all PROD’s together as shown in the OUTPUT file result. The only way to do this is using indexing within the Sterling Integrator Mapper Extended Rules.
INPUT File
SHIPTO1 PROD1 REASON01 SHIPTO1 PROD1 REASON02 SHIPTO1 PROD1 REASON03 SHIPTO1 PROD2 REASON10 SHIPTO1 PROD3 REASON20 SHIPTO1 PROD4 REASON25 SHIPTO1 PROD4 REASON26 SHIPTO1 PROD1 REASON11 SHIPTO1 PROD1 REASON12 SHIPTO1 PROD1 REASON13 SHIPTO1 PROD2 REASON30 SHIPTO1 PROD3 REASON40 SHIPTO1 PROD4 REASON27 SHIPTO1 PROD4 REASON28
OUTPUT file
E2EDP19 PROD1 E2EDP32 REASON01 E2EDP32 REASON02 E2EDP32 REASON03 E2EDP32 REASON11 E2EDP32 REASON12 E2EDP32 REASON13 E2EDP19 PROD2 E2EDP32 REASON10 E2EDP32 REASON30 E2EDP19 PROD3 E2EDP32 REASON20 E2EDP32 REASON40 E2EDP19 PROD4 E2EDP32 REASON25 E2EDP32 REASON26 E2EDP32 REASON27 E2EDP32 REASON28
Example map
Below are the rules in field PROD
Extended Rules
-------------- Pre-Session integer x; integer y; integer max_x; max_x = 0; x = 0; y = 0; REC001.PROD string[3] match; match = "no"; x=0; while x < max_x do begin x = x + 1; if $TEMP_GROUP[x].#PROD1 = #PROD then begin $TEMP_GROUP[x].#VALUE_Y = $TEMP_GROUP[x].#VALUE_Y + 1; match = "yes"; break; end end if match = "no" then begin max_x = max_x + 1; x = max_x; $TEMP_GROUP[x].#PROD1 = #PROD; $TEMP_GROUP[x].#VALUE_Y = 1; end y = $TEMP_GROUP[x].#VALUE_Y; REC001.REASON $REC003[x][y].#REASON1 = #REASON;
An explanation of what’s happening here:
“max_x” keeps a count of the number of different “PROD” values that are in the file.
This means that the “while x < max_x” loop does not execute 999 times. It only executes up to the limit of the number of new PROD values we have read so far.
x is used to store the value of the index for the temporary table that we are writing the PROD values into.
The “match” variable will have a value of “yes” if the PROD value (in the record that we are processing) already matches a PROD1 value that has been stored in the REC002 table
It has a value of “no” if we are processing a new PROD value that has not yet been stored in the temp REC002 table
The break rule is used to terminate a loop if we don’t need to execute all iterations of the loop.
So, when we have a matched PROD with PROD1, then we can exit from the loop
Once we know which occurrence of the PROD1 records table that we are updating (i.e. we have found the value of x), then we need to know how many REASONS we have already stored for that PROD.
When we find a new PROD, we can set the value of y to 1
We store the number of reasons (for each PROD) in VALUE_Y and increment it each time we process a new record for that PROD. We could probably use VALUE_Y everywhere instead of using Y.
Click here to find another example of how to use indexing rules