Count distinc not counting null as unique value

[lucaalthoff]

Hello ServiceNow Community,

I’m working on creating a scripted indicator that should return null if a specific condition isn’t met.

However, I’ve encountered an issue: the count distinct function still considers null as a unique value, which results in every null entry being displayed as 1 in the visualization.

Is there a way to make count distinct ignore entries with null values entirely?

[Bert_c1]

I suggest that your script returns an appropriate value that represents null. Such as "null", 0, -1,...

[Bhuvan]

@lucaalthoff 

Check if you have configured value when nil attribute. Can you share your script used in scripted indicator ?

Why not filter the values using Additional Conditions in the indicator configuration ?

If this helped to answer your query, please mark it helpful & accept the solution. 

Thanks,

Bhuvan

[lucaalthoff]

Hi Bhuvan,

I already considered that. Tried it with 0;"" etc. But it still counted as unique values.

Here is the script:

function checkCurrentConsistency(inc_sys_id) {
    var currentSysId = inc_sys_id;

    var ga = new GlideAggregate('incident_metric');
    ga.addQuery("inc_state!=8^mi_definition=39d43745c0a808ae0062603b77018b90^mi_valueISNOTEMPTY");
    ga.query();

    if (!ga.hasNext()) {
        return 0;
    }
    var sysIdMap = {};

    while (ga.next()) {
        var sys_id = ga.getValue("inc_sys_id");
        var miValue = ga.getValue("mi_value");

		if (!sysIdMap[sys_id]) {
			sysIdMap[sys_id] = [];
		}
		sysIdMap[sys_id].push(miValue);
	}

	if (currentSysId in sysIdMap) {
		var first = sysIdMap[currentSysId][0];
		for (var i = 1; i < sysIdMap[currentSysId].length; i++) {
			if (sysIdMap[currentSysId][i] != first) {
				return 0;
			}
		} 
		return currentSysId;
	} else {
		return 0;
	}
}

checkCurrentConsistency(current.inc_sys_id);

I also tried different return values like 0,"",null etc. but nothing seemed to work fine.

Thanks,

Luca

[JenniferRah]

I'm a little confused by your script. It seems like you aren't using GlideAggregate correctly. You may want to review the documentation here: https://developer.servicenow.com/dev.do#!/reference/api/yokohama/server/no-namespace/c_GlideAggregat...

I expected to see you creating the aggregate on the incident table. Then you would use the addAggregate to create a GROUP_CONCAT_DISTINCT aggregate. I'm not seeing that in your code.

My confusion might be because I'm not familiar with the way you're using it, and I also don't have a table in any of my instances called incident_metric, so I'm not sure if I just might not have something installed that you do.

[Chaitanya ILCR]

Hi @lucaalthoff ,

function checkCurrentConsistency(inc_sys_id) {
    var currentSysId = inc_sys_id;

    var ga = new GlideAggregate('incident_metric');
    ga.addQuery("inc_state!=8^mi_definition=39d43745c0a808ae0062603b77018b90^mi_value!=NULL");
    ga.query();

    if (!ga.hasNext()) {
        return 0;
    }
    var sysIdMap = {};

    while (ga.next()) {
        var sys_id = ga.getValue("inc_sys_id");
        var miValue = ga.getValue("mi_value");

		if (!sysIdMap[sys_id]) {
			sysIdMap[sys_id] = [];
		}
		sysIdMap[sys_id].push(miValue);
	}

	if (currentSysId in sysIdMap) {
		var first = sysIdMap[currentSysId][0];
		for (var i = 1; i < sysIdMap[currentSysId].length; i++) {
			if (sysIdMap[currentSysId][i] != first) {
				return 0;
			}
		} 
		return currentSysId;
	} else {
		return 0;
	}
}

checkCurrentConsistency(current.inc_sys_id);

try this script

@JenniferRah to your question it's not table it's a database view

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya