Build a variable with a list of items in a table without re-ordering

[annick]

Hello,

I would like to map an employee code to a group code using a regular expression.

The code below does not work: when building the fields MapExpr_EmployeeGroup and MapExpr_EmployeeRule, the items gets re-ordered. I would need them to contain the list of items without changing the order  i.e.

MapExpr_EmployeeGroup should be 'Group3','Group1','Group2','Misc' (instead of 'Group1','Group2','Group3','Misc')

MapExpr_EmployeeRule to be set as '*9','1*','2*','3*' (instead of '3*,'1*','2*','*9')

Code:

EmployeeGroups:
Load * Inline [
EmployeeGroup,EmployeeRule
'Group3', '3*'
'Group1', '1*'
'Group2', '2*'
'Misc', '*9'
] (delimiter is ',');

EmployeeGroup_MapExpr:
LOAD
concat(chr(39) & EmployeeGroup & chr(39), ',' ) as MapExpr_EmployeeGroup,
concat(chr(39) & EmployeeRule & chr(39), ',' ) as MapExpr_EmployeeRule
RESIDENT EmployeeGroups;

LET vMapExpr_EmployeeRule = peek('MapExpr_EmployeeRule', -1);
LET vMapExpr_EmployeeGroup = peek('MapExpr_EmployeeGroup', -1);

EmployeeGroupMapping:
LOAD
*,
pick(wildMatch(employee_id, $(vMapExpr_EmployeeRule)),$(vMapExpr_EmployeeGroup)) as GroupId;
LOAD
* Inline [
employee_id
'100',
'200',
'909',
'109',
'800'
];

[marcus_sommer]

concat() has a third numerical parameter which controlled the sorting. And in your case it might be the extra loaded recno().

Beside of this you may consider to replace your logic with mappings and applymap(). It should be easier - just reverse the field-order of group and rule and use a number like 1, 2, 3 instead of wildcard-searches - the call within the applymap() might be made with left(field, 1) and for the non-matching *9 you could directly add the default of misc or nesting there further logic - more mappings or other stuff.