4 Replies Latest reply: Mar 9, 2018 3:11 PM by Ryan Morgan RSS

    Add mapped column to already loaded table

    Ryan Morgan

      Hello,

       

      Hoping for some help with adding a column to an already loaded table.  I have to load the table initially to obtain the values for a subroutine to run.  When the subroutine returns it gives me an additional field to add back to the dataset.

       

       

      OriginalTable:

       

      load *;

      sql select

      searchColumn,

      otherColumn,

      otherColumn1

      from dbo.table

       


      Sub mySub (searchColumn, OriginalTable)

      \\some processes here that succesfully obtain back a new column of data

       

      ResultTable:

      searchColumn,

      newColumn

       

      MappingTable:

      mapping load searchColumn,

           newColumn

      resident ResultTable:

       

      //This part is the area where I'm struggling.  I'm trying to get the newColumn from ResultTable onto OriginalTable as an additional field.

      Load *, applymap('MappingTable', 'searchColumn', 'Not available') As newColumn resident OriginalTable;

       

       

      end sub

       

      I was able to get the end result by simply making a new table which is a combination of OriginalTable and ResultTable (via join) but I'm hoping there is a more efficient method than making an entirely new table and dropping the original.

       

       

      Thanks for any help you can provide! 

        • Re: Add mapped column to already loaded table
          vamsee duggirala

          Your mapping table looks right.

          Can you please post the error message you get.

           

          Also, no quotes for the key

           

          Applymap('MappingTable', SearchColumn, 'Not Available') as NewColumn

            • Re: Add mapped column to already loaded table
              Ryan Morgan

              Thanks for the quick reply!

               

              I'm not receiving an error message but I get a strange result after I run it:

               

              If I drop the ResultTable after applying the map then my OriginalTable values are unaffected.

               

              If I DON'T drop the ResultTable after applying the map then I get a synthetic key generated tying my ResultTable to my OriginalTable, and my OriginalTable's data ends up empty.

               

              The end result should be:
              OriginalTable:
              searchColumn, otherColumn, otherColumn1, newColumn,

              origdata,origdata,origdata,newData

               

               

              Demo Code:

               

              OriginalTable:

              Load * Inline [

              NT; Token

              ICMS; '5NNNN'

              ICNS; '5NNNM'

              ](delimiter is ';');

               

              sub LoadCredentials( vSourceTable, vSourceToken)

               

              TempTokenTable:

              Load Distinct

              $(vSourceToken) As DistinctToken

              resident $(vSourceTable);

               

              Let vRunCount = fieldvaluecount('DistinctToken');

              Let i = 0;

               

              for  i to vRunCount step 1

              Let vCurToken = Peek('DistinctToken', $(i), 'TempTokenTable');

               

              //api call that generates a result set with Token value to Credential value... below code is just to rebuild the

              //output since I can't place the api call and restructure here.

               

              next i;

               

              ResultTable:

              mapping Load * Inline [

              Token; Credential

              '5NNNN'; 'Cred1'

              '5NNNM'; 'Cred2'

              ](delimiter is ';');

               

              Load *, ApplyMap('ResultTable', $(vSourceToken) , 'Not Available') As [Credential] resident OriginalTable;

               

              Drop Table ResultTable;

               

              End Sub

               

              call LoadCredentials('OriginalTable', 'Token');

               

               

               

               

              Thanks again for taking a look at this with me.