Concatenating values of fields from field X to last field in load script

[#1]

I have the table called data with the following structure:

TRANSACTION_ID	ATT_1	ATT_2	ATT_3	CCR	EAD
X00001	a	x	h	1,000	2,500
X00002	b	y	h	2,000	5,128

I want to create a table data_keys which looks like this:

TRANSACTION_ID	MATCHING_KEY
X00001	a|x|h|
X00002	b|y|h|

The number of concatenated fields should be dependent on a variable X, so that I can automatically concatenate all the values from field X to the last loaded field.

Below is my solution, it works, but it is extremely inefficient as I am doing it with a for loop and a load inline for each single row. For instance it is impossible to load with a decent speed 1,000,000 rows.

How would you solve the problem?

Thanks.

data:

LOAD TRANSACTION_ID,

     CCR,

     EAD,

     RW,

     CVA,

     RWA,

     MET_1,

     MET_2,

     //matching attributes

     ATT_1,

     ATT_2,

     ATT_3

FROM

[.\data.xlsx]

(ooxml, embedded labels, table is data);

let data_first_attribute = 'ATT_1';

let data_attribute_start_point = 0;

for x = 1 to NoOfFields('data')

    if FieldName($(x),'data') = data_first_attribute then

        let data_attribute_start_point = x;

    end if;

next x

if data_attribute_start_point = 0 then

     call ThrowException('Error: data_first_attribute filed name was not found in data');

endif

//generating the matching key for data, it will be used to match the data with the rules

for i = 0 to NoOfRows('data')-1

    set key = '';

    for x = $(data_attribute_start_point) to NoOfFields('data')

       let key = key & peek(FieldName(x,'data'), i, 'data') & '|';

    next x

    let TRANSACTION_ID = peek('TRANSACTION_ID', i, 'data');

    data_keys:

        LOAD * INLINE [

        TRANSACTION_ID, MATCHING_KEY

        $(TRANSACTION_ID), $(key)

        ];

next i

[#2 · Accepted solution]

Marcoo,

try script from attached file
.

The only thing you should do, is to change part, where v is defined. You may use there something similiar than loop in your script, where you check what fields should be concatenated. This way you will build part of your load statemant. Next use it

regards

D