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

Thu, 12 Jun 2014 19:05:55 GMT

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

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

Thu, 12 Jun 2014 21:35:06 GMT

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

topic Re: Concatenating values of fields from field X to last field in load script in QlikView

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

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