1 Reply Latest reply: Jun 12, 2014 5:35 PM by Dariusz Mielczarek RSS

    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