Help need with Count() in case of Multiple records

[#1]

Hi All,

I need help in the below scenario.

I have a table with below fields

PatientID, Category, ClassAttended

PatientID	Category	ClassAttended
1	A	YES
1	A	NO
1	A	a*y
2	A	YES
3	A	NO
4	A	NULL

my requirement is to get the patient Count(PatientID) for ClassAttended =YES and Count(PatientID) for ClassAttended =No

But the problem here is for the same PatientID i am having multiple entry for ClassesAttended with YES,NO and Junk Values/NuLL. In this case i have to Count this PatientID only as YES. In the No category this PatientID should not be counted.

Please suggest me some solution.

Thanks in Advance

[#2]

Yes, right. everything except YES should be counted as NO, also dont count if already Counted as YES.

[#3]

This will not work if the same ID have both YES and NO. I have tried this.

[Kushal_Chawda · Accepted solution]

I think in your case count of No should be zero as For ID 1 there is already Yes

You can do it something like in attached script

Data:

Load * Inline

[

PatientID, Category, ClassAttended

1, A, YES

1 ,A, NO

1 ,A ,a*y

2 ,A ,YES

];

YesNo:

NoConcatenate

LOAD Distinct PatientID,

PatientID as PatientIDYes,

'Yes' as FlagYesNo

Resident Data

where lower(ClassAttended)='yes';

concatenate(YesNo)

LOAD Distinct PatientID,

'No' as FlagYesNo

Resident Data

where lower(ClassAttended)<>'yes'

and not Exists(PatientIDYes,PatientID);

DROP Field PatientIDYes;

Create the Chart with

Dimension:

Category

Expression

1) Yes

=Count( DISTINCT{ <FlagYesNo={'Yes'}>}PatientID)

2) No

=Count(DISTINCT{<FlagYesNo={'No'}>}PatientID)

[#5]

Hi All

the result I expect is

YES PatientID Count=2

and

NO  PatientID Count=2

Please see the below table

PatientID	Category	ClassAttended
4	A	NULL
3	A	NO
2	A	YES
1	A	YES
1	A	NO
1	A	a*y

[Kushal_Chawda]

Hi My above script is working for your data

see the attached

[tresesco]

NO  =Count({<PatientID=e({<ClassAttended={YES}>}) >} PatientID)

YES =Count({<ClassAttended={YES}>} Distinct PatientID)

[#8]

This solved my issue.Thank you so much.