Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.