Calculating Group ID and Record ID

Source Data Example

Source data example

Desired Result

Calculation result

Mapping

Mapping

Calculating Group ID

Calculation
var sGroupId :string;
sGroupName :string;
begin
sGroupId := GetVariable('vGroupID');
sGroupName := GetVariable('vGroupName');
if sGroupId='' then
begin
SetVariable('vGroupID','0');
SetVariable('vGroupName',[CATEGORYNAME]);
Result:='0';
end
 else if sGroupName<>[CATEGORYNAME] then
begin
sGroupId:=IntToStr(StrToInt(sGroupId)+1);
SetVariable('vGroupID',sGroupId);
SetVariable('vGroupName',[CATEGORYNAME]);
Result:=sGroupId;
end
else
Result:=sGroupId;
end;

Calculating Record ID

var sId :string;
sGroupName :string;
begin
sId := GetVariable('vID');
sGroupName := GetVariable('vGroupName1');
if (sId='') or (sGroupName<>[CATEGORYNAME]) then
begin
SetVariable('vID','1');
SetVariable('vGroupName1',[CATEGORYNAME]);
Result:='1';
end
 else
begin
sID:=IntToStr(StrToInt(sID)+1);
SetVariable('vID',sID);
Result:=sID;
end
end;

For more technologies supported by our ETL Software see Advanced ETL Processor Versions

Confused? Ask question on our ETL Forum
Last updated: September 17, 2022