topic Exclude records containing empty or NULL during load in QlikView

Exclude records containing empty or NULL during load

kim_rormark — Fri, 09 Sep 2011 13:39:34 GMT

Hi,

In my project I am loading data from a excel-sheet. The sheet contains ~150k rows, and in this application no records containing NULL or is empty in ANY cell are not allowed and should be excluded during load.

Is there a good way to do this?

- Kim

Exclude records containing empty or NULL during load

Fri, 09 Sep 2011 13:45:27 GMT

Hi,

a rapid solution culd be a where in this way:

Load *

from .....xls()

where len(trim(FIELD))>0;

C u,

Stefano.

Exclude records containing empty or NULL during load

its_anandrjs — Fri, 09 Sep 2011 15:52:30 GMT

Hi,

Load some thing like this in your code

LOAD

ColA,

Value

FROM

Data.xlsx

Where isnull(Value)=0 and Value>0;

to exclude 0 and blank space also it will remove both from the load

Regards,

Anand

Re: Exclude records containing empty or NULL during load

its_anandrjs — Fri, 09 Sep 2011 16:02:38 GMT

Hi,

See the attached sample data is loading some thing like this

Temp:

LOAD ColA,

Value

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet29);

Data:

Load

ColA as Field1,

Value as Field2

Resident Temp

Where isnull(Value)=0 and Value>0;

Let me know if not works

HTH

Regards,

Anand

Exclude records containing empty or NULL during load

kim_rormark — Mon, 12 Sep 2011 06:31:55 GMT

Hi Anand,

I think your solution will work, except for that ".... and Value > 0" will exclude zero-values (0), that are allowed. Only NULL and blank should be excluded. Could this maybe be fixed with ".... where isnull(Value)=0 and not Value="";" or something like that?

Exclude records containing empty or NULL during load

kim_rormark — Mon, 12 Sep 2011 06:55:08 GMT

Solution was what you said, Anand, except I had to change the Where to ... where isnull(value)=0 and not value='';

Thanks!