Count the distinct bond which have payment status is 1 for all repeating bond

[narender123]

Hello Team,

I have 2 column in my table. Bond and paymentstatus.

I want to count distinct bond for those whose paymentstatus is 1 against all same bond type.

Example:

Load * inline

[

bond,paymentstatus

a,1

a,0

a,0

a,1

b,1

b,1

c,1

c,0

d,1

d,1

];

The result should be  2 as bond b and d has payment status 1 for the same bond type.

Thanks,

Narender

[sunny_talwar · Accepted solution]

Try this

=Count(DISTINCT {<bond = e({<paymentstatus -= {'1'}>})>} bond)

[narender123]

Hello Sunny,

Thank you, It is working fine.

Can we use possible records p() rather than exclude e().

like:

=Count(DISTINCT {<bond = p({<paymentstatus = {'1'}>})>} bond)

This is showing wrong data. Can you tell me?

Narender

[sunny_talwar]

This won't work because it will include bond where paymentstatus = 1 even once

[narender123 · Accepted solution]

ok, Thanks.

I used 1 more expression that is also giving the right result.

=count({<productstatus={1},bond={"=count(distinct productstatus)=1"}>}distinct bond)

Thank you.

Narender