Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Excluding "empty" rows

I have a table which has 7 fields and it is getting additional rows where the first field is filled but the fields 2-6 are blank.

I need to load (resident) ONLY the rows where any one of fields 2-6 are are complete.

I have tried (and had no success) with "LOAD * Resident CSRelated ...

     where not isnull(Field1) and

(not isnull(Field3) or
not isnull(Field4) or
not isnull(Field5) or
not isnull(Field6) or
not isnull(Field7))

     ;

or

       where len(Field2&Field3&Field4&Field5&Field6&Field7)>0;

or any number of other permutations.

Suggestions please. Thanks

@excluderow

2 Replies
hemhund2016
Contributor

Re: Excluding "empty" rows

Hi Derek,

You can use like below.

CSRelated_New:

LOAD * Resident CSRelated

where not isnull(Prod_INSTANCE_ID)

or Prod_INSTANCE_ID<>'-';

Drop table CSRelated;

Thanks,

Hemanth.

MVP
MVP

Re: Excluding "empty" rows

You could try to check against LEN(TRIM(FIELD)) instead of NOT ISNULL(FIELD), but as far as I interpret your screen shot, that should not perform differently here.

Besides that, take care to do a NOCONCATENATE LOAD when you do a LOAD * RESIDENT ..., to avoid auto-concatenation of records.