Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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