Solved: Help need with Count() in case of Multiple records - Qlik Community

Report Inappropriate Content · ‎2015-12-03

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

Kushal_Chawda · ‎2015-12-03

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)

View solution in original post

tresesco · ‎2015-12-03

For YES

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

For NO

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

Check if you need Distinct.

jonathandienst · ‎2015-12-03

Yes (everything except NO):

Count({<ClassAttended -= {'NO'}>} Distinct PatientID)

No

Count({<ClassAttended = {'NO'}>} Distinct PatientID)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2015-12-03

Yes

Count Only YES exclude everything Else

No

Count Everything Except YES. Ignore the PatientID if counted under YES

Anonymous · ‎2015-12-03

try this:

In case of YES:

= count({<ClassAttended={'YES'}>} distinct PatientID)

In case of NO:

= count({<ClassAttended={'NO'}>} distinct PatientID)

nagarjuna_kotha · ‎2015-12-03

Hi Saritha,,

One way is Writing Expression like Jonathan and Tresesco.

Or you can also do like this.

PFA....

Anonymous · ‎2015-12-03

Hi,

Try below:

For Yes:

Count({<ClassAttended -= {'NO'}>} Distinct PatientID)

OR

Count({<ClassAttended = {*}-{'NO'}>} Distinct PatientID)

For No:

Count({<ClassAttended = {'NO'}>} Distinct PatientID)

Thanks.

nagarjuna_kotha · ‎2015-12-03

Yes :

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

No

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

Or

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

-Nagarjuna

Report Inappropriate Content · ‎2015-12-03

Hi,

the PatientID should not be counted under both YES and No if it was already counted as YES. Ignore the No case if counted under as YES.

Kushal_Chawda · ‎2015-12-03

Wan to count Junk values also in NO?