Trim all fields at load time...

[#1]

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..

[flipside · Accepted solution]

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

[#3]

Awesome!!! It works and it saved me a lot of time and effort. Thanks Flipside