2 Replies Latest reply: Feb 19, 2013 12:17 AM by Sanad M RSS

    Trim all fields at load time...

      Hi all,

       

       

      I'm trying to trim all fields at load time using fieldname function but I'm having trouble getting the alias to work:

       

       

       

      LOAD * FROM AMBS.qvd(qvd);
      
      Let i=1;
      
      for i=1 to NoOfFields('Table1')
      
      Table_Trimmed:
      
      LOAD
      
      trim(FieldName($(i),'Table1')) as FieldName($(i),'Table1')
      
      Resident Table1;
      
      
      NEXT i;
      
      
      DROP Table Table1;
      
      

       

       

      I'm getting an error while trying to use the fieldname function in the alias (after the 'as' keyword). Was wondering if anyone was able to crack this.

      Thanks..

        • Re: Trim all fields at load time...
          Dave Riley

          You can build your load script and store it into a variable as this ...

           

          Table1:

          LOAD * FROM AMBS.qvd(qvd);

           

          Let i=1;

          Let script='';

           

          for i=1 to NoOfFields('Table1')

           

          script = script & ' Trim(' & FieldName($(i),'Table1') & ') as ' & FieldName($(i),'Table1') & ',';

           

          NEXT i;

           

          script = left('$(script)',len('$(script)')-1); // removes unwanted final comma

           

          Table_Trimmed:

          noconcatenate Load $(script) resident Table1;

           

          DROP Table Table1;

           

          The variable is 'executed' outside the loop.

           

          flipside