Glide aggregate max

sowmyaj · ‎01-14-2016

Hi All,

I'm not able to get the maximum count.

Based on my requirement I have result as 'A(value) - 3(count)' , 'B(value) - 3(count)', 'C(value) - 6(count)', 'D(value) - 10(count)', 'E(value) - 2(count)', 'F(value) - 1(count)','G(value) - 7(count)'.

Now, I need to find the MAX of above result. Also I need to display top "FIVE" MAX count.

Initial step I tried as below to get the "MAX" count but didn't get the expected result.

<g:evaluate>

var inc=new GlideAggregate('incident');

inc.addQuery( My queries )

inc.addAggregate('COUNT','abc');

inc.addAggregate('MAX','abc');

inc.query();

</g:evaluate>

<j:while test="${inc.next()}">

${inc.getAggregate('COUNT', 'abc')}

${inc.getAggregate('MAX', 'abc')}

</j:while>

Can anyone help me to resolve this!!!!

Thanks,

Sowmya

sergiu_panaite · ‎01-18-2016

Hi Sowmya,

Can you try something like:

<?xml version="1.0" encoding="utf-8" ?>

<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">

 <g:evaluate>

   var count = new GlideAggregate('incident'); 

   count.addAggregate('COUNT','abc'); 

   count.orderByAggregate('COUNT','abc'); 

   count.query(); 

   var maxloops = 0;     

 </g:evaluate>



 <j:while test="${count.next() &amp;&amp; maxloops ${AMP}lt; 5}">   

   ${count.getAggregate('COUNT', 'abc')}

   ${maxloops += 1}

 </j:while>

</j:jelly>

Regards,

Sergiu

View solution in original post

sergiu_panaite · ‎01-14-2016

Can you elaborate a bit on "I tried as below to get the "MAX" count but didn't get the expected result" ?

What did you expected and what did you got as result? Your requirement is not clear to me.

Regards,

Sergiu

sowmyaj · ‎01-17-2016

Hi Sergiu,

In my table, I have records in column "abc" . I need to find the "MAXIMUM" count of the record and also "top five".

EX. In "abc" column I have "a,b,d,a,c,I,j,b,a,a,d,r,s,t,t,u,v,v" values

My expected output should be

Max count value is ''a" and top five count values are "a,b,d,t,v".

Please let me know how can I achieve this!!!!!

Thanks,

Sowmya

Vinobalu · ‎01-17-2016

Hi Sowmya,

Try this below code.

var gr = new GlideRecord('incident');

gr.orderByDesc('number');

gr.setLimit(5);

gr.query();

while(gr.next())

gs.addInfoMessage('Value :'+gr.number)

sergiu_panaite · ‎01-18-2016

Hi Sowmya,

I've used incident table and 'priority' field to build up an example for what you need. See below script:

var count = new GlideAggregate('incident');

count.addAggregate('COUNT', 'priority');

count.orderByAggregate('COUNT', 'priority');

count.query();     

maxloop = 0;

while (count.next() && (maxloop < 5)) { 

       var max = count.getAggregate('COUNT', 'priority');

       gs.print('Priority: ' + count.priority + ' has: ' + max + ' incidents');

       maxloop += 1;

}

Running this script via Background Scripts I get:

[0:00:00.016] Script completed in scope global: script

*** Script: Priority: 1 has: 25 incidents
*** Script: Priority: 5 has: 12 incidents
*** Script: Priority: 3 has: 6 incidents
*** Script: Priority: 2 has: 4 incidents
*** Script: Priority: 4 has: 3 incidents

So, for field 'priority' (in your case it would be 'abc') I am displaying the max number of incidents for a priority (in your case for the value that has max count) and then the next 4 top counts (5 in total).

Can you please adjust it to your case and see if it works as you expect?

Regards,

Sergiu