Need help - Using count distinct clause to eliminate repetitive data

Support - Assistance technique
1

Hi all,

I have created an event program where one of the data elements is beneficiary code. The beneficiary code is a text data type. My use case says the same code can be entered several times and what I want is to create a program indicator to eliminate the repetitive appearance of the same data. Like using DISTINCT clause to count the code only once.

e.g.
image

From the above example, I should get 8, not 12 as a result because the beneficiary code is repeated in some cells.

I am wondering if any one form the community help me create the program indicator.

Thanks

2

hi @fernando , you might want to follow (and upvote) this jira issue 5934 as distinct count d2 functions are not available (yet).

3

Hey guys - you can have a look at this thread - using custom aggregation type to count distinct values:

4

Hi @dhuser,

I have voted the issue.

Thanks

5

Hi @Markus,

I can not access the link. I get the following message: ‘The page doesn’t exist or is private.’

Thanks

6

H @fernando. seems like the topic was deleted by accident, I undeleted it. Can you see the topic now?

7

Hi @Karoline,

I used the following syntax as suggested by @Markus, but it is not working:

count(distinct #{dataElement})

Thanks

8

Hi @fernando,
How are you? I see you edited the post from December, are you still facing an issue? Please let me know so I follow up with this.

@dhis2-tracker team would you check this post please.

9

Hi @Gassim,

Yes, I am still facing the issue. I never got the solution.

Thanks

10

Hi @Gassim,

I am facing this issue too. using DHIS2 2.31.7.
PI:

  1. Expression

    image
    image1048×778 31.9 KB

  2. Aggregate type
    image

My objective, need to count distinct data on program stage. But when go to pivot table, got nothing.

Thanks.

11

Ditto @fernando and @LCaynar in 2.35.3, distinct count is not functional. Definitely agree that this would be very valuable.

1 Like
12

Hi there @LCaynar and @Matthew_Boddie ,
There was a slight mixup in the expression syntax above. When using te “CUSTOM” aggregation type, you would have to insert the aggregation keyword around the expression like this:

count(distinct #{dataElement})

1 Like
13

I have tried this and commented in the post Program indicator for count of unique/distinct values - #17 by Matthew_Boddie (as have others) on this not being functional. Though perhaps we are doing something incorrect?

14

Thanks - It does indeed seem like a bug. You can follow the ticket here: [DHIS2-11327] - Jira

1 Like
15

Came back to this and seeing now that this is done as of a few months ago! Excited for future patches that will include the distinct count fix.

CC @lorimetz4 @jillianberkowitz @chase.freeman @megankill @dhuser

3 Likes
16

@Matthew_Boddie thanks, isn’t it already available in 2.38.0 and the latest releases? Or do you mean when you update/upgrade your instance/s?

Thanks!

17

2.36.11, 2.37.7, and 2.35.14 aren’t released yet to my knowledge, which is where the distinct count is noted to be effective-- right @Gassim ?

1 Like
18

Thanks Matthew! From Jim Grace’s comments in the Jira issue you shared, it has been back ported to 2.36.10 (released) and 2.37.5 (2.37.6 is the latest release). And, yes, 2.35.14 hasn’t been released yet so if you’re on 2.35, you’re right you’d need to wait. :pray:

19

I can confirm that this functionality does exist! Very much looking forward to testing and seeing how far I can take this use (for example, if I can concatenate values within the distinct (count()) expression.

2 Likes
20

Unfortunately this only works for Number fields—or at least I can say definitively that it does not work for text fields. Without string functionality there isn’t much value in this for my use case. Made a note on the ticket in hopes Text fields can also be included!

2 Likes