topic Re: Trim all fields at load time... in QlikView

Trim all fields at load time...

Mon, 18 Feb 2013 13:29:06 GMT

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

flipside — Mon, 18 Feb 2013 14:05:42 GMT

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

Re: Trim all fields at load time...

Tue, 19 Feb 2013 05:17:09 GMT

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