How to ignore same unique record being considered for the intended logic

[nevilledhamsiri]

Dear All,

There are some records among them some are repeated twice. one of them has  B/CF  grater than zero  while other record with B/CF more than zero. If I need to select records of which B/CF =0  but  C/B >0, the above record too is coming. How should the expression  be modified to ignore the below record which has a B/CF >0  at one of its row.

BR_CODE	CLAIM NUMBER	B/BF	PROVISION	PAID	ADJUSTMENT	B/CF	RECVERY_TYPE	O/B	MN	RECEIVED	C/B
RA	CLRA001A00025	25000	0	0	0	25000	FOREIGN	4500	0	0	4500
RA	CLRA001A00025	0		0	0	0	REINSURANCE	6500	0	0	6500

Expression used as follows:

SUM({<[B/CF]={"=[B/CF]=0"},[C/B]={"=[C/B]>0"}>}[C/B])

Thanks in advance

Neville

[Chanty4u]

Try this

SUM({<isnull([B/CF])=0},[C/B]={">0"}>}[C/B])

[nevilledhamsiri]

Looks like it doesn't work. There is an error in the formula .could you please test it & send?

[MayilVahanan · Accepted solution]

Hi @nevilledhamsiri 

Try like this

SUM({<[CLAIM NUMBER]={"=Sum([B/CF])=0 and Sum([C/B])>0"}>}[C/B])

[Vegar]

It looks like you can simplify your expression. This should work. 

=SUM( {<[B/CF]={"0"},[C/B]={">0"}>} [C/B])

[nevilledhamsiri]

Dear Vahanam

Thank for the formula which helped me to resolve my issue. The good thing about is that even if the B/CF carries a null value, still the expected out put is produced.

Regards

Neville