Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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
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
Awesome!!! It works and it saved me a lot of time and effort. Thanks Flipside