Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Trim all fields at load time...

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

2 Replies
flipside
Valued Contributor II

Re: Trim all fields at load time...

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

Not applicable

Re: Trim all fields at load time...

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

Community Browser