Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator II
Creator 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:
Load
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:

Load *
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

 

 

1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

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:
NoConcatenate load
$(vString)
Resident Table;

drop table Table;

View solution in original post

4 Replies
lorenzoconforti
Specialist II
Specialist II

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

 

Peony
Creator II
Creator II

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. 

lorenzoconforti
Specialist II
Specialist II

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:
NoConcatenate load
$(vString)
Resident Table;

drop table Table;

View solution in original post

Peony
Creator II
Creator II

Prefect solution! Thank you, Lorenzo!