Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

I have the table called data with the following structure:

TRANSACTION_IDATT_1ATT_2ATT_3CCREAD
X00001axh        1,000         2,500
X00002byh        2,000         5,128


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

TRANSACTION_IDMATCHING_KEY
X00001a|x|h|
X00002b|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

1 Solution

Accepted Solutions
Not applicable

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

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

1 Reply
Not applicable

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

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