...

View Full Version : How can I group by Branch, then by Client and then by Referral Type in JavaScript.



asifakhtar
08-26-2010, 09:04 PM
Hi,
I have an array in JavaScript. How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaScript. Please note I need to do this client side using JavaScript.

Array Data:

Branch Client Referral Type LPS #
402036 402430 Psychological File Review 30
402049 402805 In-Home Assessment 10.87927
402050 402993 Chiropractic Assessment 100
402049 402805 Chiropractic File Review 10.88054
402049 402806 Chiropractic File Review 10.88055
402049 402806 Defense Medical 20
402049 402807 Chiropractic Assessment 10
402049 402807 In-Home Assessment + Form 1 10.88054
402049 402807 Physiotherapy Assessment 10
402049 402808 Attendant Care Assessment 10
402049 402808 Chiropractic Assessment 10.88041
402049 402808 In-Home Assessment 10
402049 402816 Chiropractic Assessment 10.85316
402049 402827 Neurological File Review 10.88047
402049 402827 Social Work File Review 10.88044
402050 402605 Psychological File Review 880434
402050 402661 Physician Assessment 878203
402050 402993 Physician Assessment 1000
402036 402575 Psychological File Review 50

Thanks

Old Pedant
08-26-2010, 09:34 PM
Start by putting the data into an array of objects:


<script>
function Info( b, c, r, lp )
{
this.branch = b;
this.client = c;
this.referral = r;
this.lps = lp;
}

var allInfo = [
new Info("402036","402430","Psychological File Review","30" ),
new Info("402049","402805","In-Home Assessment","10.87927" ),
... etc ...
(no comma after last one)
];

function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// all three same:
return 0;
}

var sortedInfo = allInfo.sort( compareInfo );

for ( var i = 0; i < sortedInfo.length; ++i )
{
var info = sortedInfo[i];
document.write( ... format info as you want ... );
}
...

For COUNT DISTINCT LPS #, do you mean distinct no matter what branch/client/referral? Or do you mean grouped by one or more of those?

asifakhtar
08-26-2010, 10:01 PM
Start by putting the data into an array of objects:


<script>
function Info( b, c, r, lp )
{
this.branch = b;
this.client = c;
this.referral = r;
this.lps = lp;
}

var allInfo = [
new Info("402036","402430","Psychological File Review","30" ),
new Info("402049","402805","In-Home Assessment","10.87927" ),
... etc ...
(no comma after last one)
];

function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// all three same:
return 0;
}

var sortedInfo = allInfo.sort( compareInfo );

for ( var i = 0; i < sortedInfo.length; ++i )
{
var info = sortedInfo[i];
document.write( ... format info as you want ... );
}
...

For COUNT DISTINCT LPS #, do you mean distinct no matter what branch/client/referral? Or do you mean grouped by one or more of those?

Thank you very much for the reply. I will test your code and see if it works.

It should COUNT DISTINCT LPS # after all the group by are dome.

Again thank you.

Old Pedant
08-26-2010, 11:15 PM
Let me reword my question.

*IF* you were doing this in SQL, instead of in JS code, do you want



SELECT COUNT(DISTINCT LPS) FROM table;

or do you want


SELECT branch, region, referral, COUNT(DISTINCT LPS)
FROM table
GROUP BY branch, region, referral

Or something else???

Show me the SQL equivalent of what you want.

asifakhtar
08-26-2010, 11:28 PM
Let me reword my question.

*IF* you were doing this in SQL, instead of in JS code, do you want



SELECT COUNT(DISTINCT LPS) FROM table;

or do you want


SELECT branch, region, referral, COUNT(DISTINCT LPS)
FROM table
GROUP BY branch, region, referral

Or something else???

Show me the SQL equivalent of what you want.

I want
SELECT branch, region, referral, COUNT(DISTINCT LPS)
FROM table
GROUP BY branch, region, referral

Please note my array is dynamic.

Old Pedant
08-27-2010, 12:19 AM
Please note my array is dynamic.
??? What is that supposed to mean?

You mean the user adds to it via code on the HTML page?

Old Pedant
08-27-2010, 12:22 AM
I want
SELECT branch, region, referral, COUNT(DISTINCT LPS)
FROM table
GROUP BY branch, region, referral

Okay, so just extend that compareInfo function so that it also sorts by LPS:


function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// all three same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}

And then it will be easy to get the count of DISTINCT values by simply noting when the various fiels in the array change.

asifakhtar
08-28-2010, 12:47 AM
Okay, so just extend that compareInfo function so that it also sorts by LPS:


function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// all three same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}

And then it will be easy to get the count of DISTINCT values by simply noting when the various fiels in the array change.

Thank you very much. I tested it today and it works. Getting COUNT DISTINCT LPS # wasn't easy too but I manage. Thank you for your effort.

asifakhtar
09-07-2010, 03:28 PM
Okay, so just extend that compareInfo function so that it also sorts by LPS:


function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// all three same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}

And then it will be easy to get the count of DISTINCT values by simply noting when the various fiels in the array change.

Hi, I used your approach and now I have branch, region, referral in order but the way I am doing it, I am only getting branch count when I am counting LPS.
How can I get the result for the following SQL statement using JavaScript.

SELECT branch, region, referral, COUNT(DISTINCT LPS) FROM table GROUP BY branch, region, referral

Old Pedant
09-07-2010, 08:34 PM
Okay...this is *NOT TESTED*.

But since you say the other code seemed to work, maybe this will work, too.



var allInfo = [
new Info("402036","402430","Psychological File Review","30" ),
new Info("402049","402805","In-Home Assessment","10.87927" ),
... etc ...
(no comma after last one)
];

function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// three the same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}

var sortedInfo = allInfo.sort( compareInfo );

var info = sortedInfo[0];
priorBranch = info.branch;
priorClient = info.client;
priorReferral = info.referral;
priorLps = info.lps;
lpsCount = 0;

for ( var i = 0; i < sortedInfo.length; ++i )
{
info = sortedInfo[i];
curBranch = info.branch;
curClient = info.client;
curReferral = info.referral;
curLps = info.lps;
if ( curBranch != priorBranch
|| curClient != priorClient
|| curReferral != priorReferral
|| curLps != priorLps
) {
writeRow( priorBranch, priorClient, priorReferral, priorLps, lpsCount );
priorBranch = curBranch;
priorClient = curClient
priorReferral = curReferral;
priorLps = curLps;
lpsCount = 0;
}
// then whether changed or not, we bump the count:
++lpsCount;
}
// write last row:
writeRow( priorBranch, priorClient, priorReferral, priorLps, lpsCount );

// THIS IS JUST A SAMPLE
// of what you *MIGHT* use for writeRow:
function writeRow( br, cl, ref, lps, cnt )
{
document.write(
"<tr>" +
"<td>" + br + "</td>" +
"<td>" + cl + "</td>" +
"<td>" + ref + "</td>" +
"<td>" + lps + "</td>" +
"<td>" + cnt + "</td>" +
"</tr>" );
}

As you can see, we do *NOT* write a row until we have gotten a *CHANGE* in one or more of the values. That way, we can get an accurate count of the UNIQUE valules (that is, where all 4 fields match).

This means we have to have a "clean up" write of the last row, as shown there, so it will work better to have the writeRow( ) be a separate function.

Old Pedant
09-07-2010, 08:40 PM
OH! Sorry!!! That will *SHOW* all of the DISTINCT LPS values, not just count them!!!

Needs minor fix to match the SQL you showed...hang on...

Okay, try again...I think this works:



var allInfo = [
new Info("402036","402430","Psychological File Review","30" ),
new Info("402049","402805","In-Home Assessment","10.87927" ),
... etc ...
(no comma after last one)
];

function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// three the same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}

var sortedInfo = allInfo.sort( compareInfo );

var info = sortedInfo[0];
priorBranch = info.branch;
priorClient = info.client;
priorReferral = info.referral;
priorLps = info.lps;
lpsCount = 1;

for ( var i = 0; i < sortedInfo.length; ++i )
{
info = sortedInfo[i];
curBranch = info.branch;
curClient = info.client;
curReferral = info.referral;
curLps = info.lps;
if ( curBranch != priorBranch
|| curClient != priorClient
|| curReferral != priorReferral
) {
writeRow( priorBranch, priorClient, priorReferral, lpsCount );
priorBranch = curBranch;
priorClient = curClient
priorReferral = curReferral;
priorLps = curLps;
lpsCount = 1;
} else if ( curLps != priorLps )
++lpsCount;
}
}
// write last row:
writeRow( priorBranch, priorClient, priorReferral, lpsCount );

// THIS IS JUST A SAMPLE
// of what you *MIGHT* use for writeRow:
function writeRow( br, cl, ref, lps, cnt )
{
document.write(
"<tr>" +
"<td>" + br + "</td>" +
"<td>" + cl + "</td>" +
"<td>" + ref + "</td>" +
"<td>" + cnt + "</td>" +
"</tr>" );
}


BUT UNTESTED!

asifakhtar
10-05-2010, 09:49 PM
First please accept my apology for getting back to you late. Second your solution works. U Rock.
Thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum