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

    Add mapped column to already loaded table

    Ryan Morgan



      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.





      load *;

      sql select




      from dbo.table


      Sub mySub (searchColumn, OriginalTable)

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







      mapping load searchColumn,


      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:
              searchColumn, otherColumn, otherColumn1, newColumn,




              Demo Code:



              Load * Inline [

              NT; Token

              ICMS; '5NNNN'

              ICNS; '5NNNM'

              ](delimiter is ';');


              sub LoadCredentials( vSourceTable, vSourceToken)



              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;



              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.