Help with a Count Distinct Script

[Rohit8]

Hello,

I am have created an indicator to get a total count of users. The table has a lot of users appearing multiple times. So I need to do a count distinct to get a total count of unique "user_name" records. I am not that proficient in Javascript yet. 

This is the script I added:

var ga = new GlideAggregate('u_removable_media_exception_request');
ga.addAggregate('COUNT(DISTINCT', 'user_name');
ga.setGroup(false);
ga.query();
ga.next();
gs.info(ga.getAggregate('COUNT(DISTINCT', 'user_name'));

And I keep getting the below error during Job collection. It fails and collects 0 records.

Could you guys help me find out what am I doing wrong or suggest a better way to do this.

Thanks a lot.

[Adam Stout · Accepted solution]

Let's solve that problem then.  Use a COUNT Distinct and for the script, just put in the fully qualified field name.  Select the argument for the script to be the field you want to count distinct (Requestor.User Name?)

Then your script will be one line (I'm guessing on the field names, so double check that):

current.requestor.user_name

That should do the trick.  This allows you to do aggregates on dot walked fields.

[Rohit8]

Thanks a lot, Adam. That worked just like I needed it to.

Appreciate your help. 🙂

[Josh Cooper]

And more to the point, you don't seem to be actually returning anything.  You've logged an answer, but PA expects the score value to be 'left' when you're done.

The way I do it is to wrap that whole script into a function:

getScore();

function getScore(){ 

**put all your other stuff here, and change gs.info to 'return'**

}

But to Adam's point, if you just want a distinct user count, set the Aggregate choicelist to Count Distinct.  It should be at the bottom there, and then you just pick 'user_name' as the field.

[Adam Stout]

If you are using SUM/AVG/MIN/MAX your script needs to return a number, but for COUNT DISTINCT, it can be a string.  You can't dot walk for any aggregate in PA through the UI, but you can by simply adding a script that returns the desired field in one line.