Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
narender123
Specialist
Specialist

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

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

2 Solutions

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

narender123
Specialist
Specialist
Author

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

View solution in original post

4 Replies
sunny_talwar

Try this

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

View solution in original post

narender123
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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

View solution in original post