# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

## How to optimize script values using loop?

Hi all.

I have a table where some of fields may contain no data. I want to be sure that in this case field value will be Null.

Normally the solution looks like this:

table:
If(Len(Trim([Field 1]))=0, Null(), [Field 1]) as [Field 1],
If(Len(Trim([Field 2]))=0, Null(), [Field 2]) as [Field 2],
If(Len(Trim([Field 3]))=0, Null(), [Field 3]) as [Field 3],
If(Len(Trim([Field 4]))=0, Null(), [Field 4]) as [Field 4]
From table.qvd(qvd);

I tried to proceed each field values using loop but have no luck. Could you, please , give some advice regarding the solution?

I suppose the solution should be something like this:

From  Table.qvd (qvd);

For i = 1 to NoOfFields('Table')

Let vFieldName = FieldName(\$(i), 'Table');
Let vFieldValues = FieldValue(\$(vFieldName), 1);

If
(
Len(Trim(\$(vFieldValues))) = 0,
Null(),
\$(vFieldValues)
)

Next i

Labels (4)

• ### Looping variable script

1 Solution

Accepted Solutions
Highlighted
Valued Contributor II

## Re: How to optimize script values using loop?

I see. Try this then

Let vCount =NoOfFields('Table');

set vString = '';

FOR i=1 to \$(vCount)

let vFieldName = FieldName(i ,'Table');

if i = 1 then
set vString = If(Len(Trim(\$(vFieldName)))=0, Null(), \$(vFieldName)) as \$(vFieldName) ;
else
set vString = \$(vString), If(Len(Trim(\$(vFieldName)))=0, Null(), \$(vFieldName)) as \$(vFieldName) ;
end if

Next

Table2:
\$(vString)
Resident Table;

drop table Table;

4 Replies
Highlighted
Valued Contributor II

## Re: How to optimize script values using loop?

Why don't you just apply a different null interpreter?

SET NullInterpret = '';

NULLASVALUE *; //list all fields you want to check this on or * for all

NULLASNULL *; //switch null as value off in case you don't want it applied to the subsequent tables

Highlighted
Contributor II

## Re: How to optimize script values using loop?

Hi Lorenzo.

This function unfortunately don't work for me. For some reason, some of cells  that contains no data didn't turns into clear Null . They are still stays empty and turning into Null only after If condition, that I described in the example upper.

Highlighted
Valued Contributor II

## Re: How to optimize script values using loop?

I see. Try this then

Let vCount =NoOfFields('Table');

set vString = '';

FOR i=1 to \$(vCount)

let vFieldName = FieldName(i ,'Table');

if i = 1 then
set vString = If(Len(Trim(\$(vFieldName)))=0, Null(), \$(vFieldName)) as \$(vFieldName) ;
else
set vString = \$(vString), If(Len(Trim(\$(vFieldName)))=0, Null(), \$(vFieldName)) as \$(vFieldName) ;
end if

Next

Table2: