Ab Initio : Playing with Vectors

The requirement is prepare the list of Products separated by '~'  for each Customer ID. The sample data looks like below,

Input Data

Cust ID	Product
101	AA
101	AB
101	AC
102	AA
102	AC

Output :

Cust ID	Product
101	AA~AB~AC
102	AA~AC

Solution :
The ROOLUP is used with input SORTED data on Cust ID.

Delimited String Vector is defined and value is assigned to the same by using below functions.

  let  string("\x02", maximum_length=7) [unsigned long] vector_final;
  vector_final = vector_sort_dedup_first(accumulation(in.product) , {machine descending}  );
  out.product:: string_join(vector_final, '~' );

accumulation :   Returns a vector of input values. Available only within the ROLLUP and SCAN components.

vector_sort_dedup_first : Returns a new vector consisting of a sorted version of the input vector, which includes only the first element in each group of elements whose key matches the specified key.

string_join: Concatenates vector string elements into a single string.

Predefined sort sequence modifiers

DML supports the following predefined sort sequence modifiers:

Modifier	Description
phonebook	Treats digits as the lowest-value characters, followed by the letters of the alphabet in the order AaBbCcDd..., followed by spaces. Ignores all other characters, such as punctuation. The order of digits is 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
index	Orders the same as phonebook, except that punctuation characters have lower values than all other characters and are not ignored. The order of punctuation characters is the machine sequence.
machine	Orders character code values in the sequence in which they are arranged in the character set of the string: ASCII-based character sets — Digits are the lowest-value characters, followed by uppercase letters, followed by lowercase letters. EBCDIC character sets — Lowercase letters are the lowest-value characters, followed by uppercase, followed by digits. Unicode character sets — The order is from the lowest character code value to the highest.