topic Excluding "empty" rows in QlikView

Excluding "empty" rows

Wed, 09 Dec 2015 18:43:55 GMT

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

;

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

or any number of other permutations.

Suggestions please. Thanks

@excluderow

Re: Excluding "empty" rows

hemhund2016 — Wed, 09 Dec 2015 18:53:08 GMT

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.

Re: Excluding "empty" rows

swuehl — Wed, 09 Dec 2015 18:54:45 GMT

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.