count fields

[#1]

hi all,

in a table with 5 fields:

ID,

A,

B,

C,

D

where fields A, B, C, D have numerical values, i want to add a field to this table in my LOAD script telling me the number of fields (only A, B, C and D) that are 0.

Example:

ID	A	B	C	D
XYZ	5	0	2	1

Adding the niew field the table shall look like this:

ID	A	B	C	D	# of zeros
XYZ	5	0	2	1	1

Thanks a lot for some ideas!

[swuehl · Accepted solution]

LOAD

ID,

A,

B,

C,

D,

-1*rangesum(A=0,B=0,C=0, D=0) as NrOfZeros

from Table;

[CELAMBARASAN]

Hi,

     Try with this

Load

       ID,

       A,

     B,

     C,

     D,

      RangeSum(If(A=0,1),If(B=0,1),If(C=0,1),If(B=0,1)) as NumCount

From..

Celambarasan

[jagan]

Hi,

Use the following script, with a little change to Stefan's script.  Used fabs() instead -1*

LOAD

*,

fabs(RangeSum(A=0, B=0, C=0, D=0)) AS zeroCount;

LOAD * INLINE [

    A, B, C, D

    1, 0, 0, 1

    76, 0, 8, 7

    0, 0, 0, 0

];

Regards,

Jagan.