[Clug-l] Sort and aggregate nodes help

Tue May 6 04:32:57 EDT 2008

Mark,

Your type of problem is quite easy to resolve if you continue to use the Aggregate node to acquire the min or max date (first instance) and inner join this back with your data.  By using the Aggregate node to summarise and get the min or max of dates it is possible to get the first or last row (as long as there aren't complete duplicates).

The trick I suspect you are missing is that in each incremental aggregate you should use the prior aggregate node max field in the key selection.  Difficult to explain, but hopefully the attached example zipped stream and data will clearly illustrate.

I use this method a lot because all my data is historical (I work in telco) and there are many account changes over time for each customer.  This method pushes back as SQL on a database and is a very efficient way to rank data to obtain the most recent, first, maximum etc row (on indexed tables and a well structured database).

Cheers

Tim Manns

-----Original Message-----
From: clug-l-bounces at spss.com [mailto:clug-l-bounces at spss.com] On Behalf Of Mark Palmberg
Sent: Saturday, 3 May 2008 12:04 PM
To: Susan Foltz Boklage
Cc: clug-l at spss.com
Subject: Re: [Clug-l] Clug-l Digest, Vol 27, Issue 7

Hi, Sue.

Thanks for this reply (you too, Hayo).  I actually used an aggregate node to aggregate on ID and then selected MIN and MAX for gift_dat, which gave me my first and last gift dates for each record.

The Distinct node did indeed come in handy for picking out the amount of the earliest gift, too.  Thanks for that tip.  I still can't seem to get the first date of the largest gift, though.  I sort my file on ID (ascending) and then AMT (descending), then I use a Distinct node to "include" the first instance of ID.  But it's not giving me the first instance of the largest gift.  It appears to return one of the instances of the largest gift, but not the first date of the largest gift (for those donors who have given their max gift amount on multiple dates).  Is this making any sense?

Thanks again for your thoughts.

Mark
2008/4/30 Susan Foltz Boklage <susieq324 at yahoo.com<mailto:susieq324 at yahoo.com>>:

Mark,

For your earliest gift, this is actually quite easy using your sort node, so that everything is in ascending date order, and then you can use a Distinct node to select the first date record.  When you setup your Distinct node, remove the date field so that it will return the first amount record for each entity.

You can probably use a similar tactic for your largest gift, except here, you would want to remove the Date field from the distinct node, once your data is sorted in descending Amount order.

An aggregate node may be needed if you're looking to roll up all gifts made on any given date but I'm assuming that these are individual contributors and you're looking to find more details about their giving history for each donor.

Good luck!

Sue

----- Original Message ----
From: "clug-l-request at spss.com<mailto:clug-l-request at spss.com>" <clug-l-request at spss.com<mailto:clug-l-request at spss.com>>
To: clug-l at spss.com<mailto:clug-l at spss.com>
Sent: Wednesday, April 30, 2008 12:00:03 PM
Subject: Clug-l Digest, Vol 27, Issue 7

Send Clug-l mailing list submissions to
    clug-l at spss.com<mailto:clug-l at spss.com>

To subscribe or unsubscribe via the World Wide Web, visit
    http://cammlist1.spss.com/mailman/listinfo/clug-l
or, via email, send a message with subject or body 'help' to
    clug-l-request at spss.com<mailto:clug-l-request at spss.com>

You can reach the person managing the list at
    clug-l-owner at spss.com<mailto:clug-l-owner at spss.com>

When replying, please edit your Subject line so it is more specific
than "Re: Contents of Clug-l digest..."

Today's Topics:

  1. Sort and aggregate nodes help (Mark Palmberg)

----------------------------------------------------------------------

Message: 1
Date: Tue, 29 Apr 2008 16:49:22 -0500
From: "Mark Palmberg" <palmberg at gmail.com<mailto:palmberg at gmail.com>>
Subject: [Clug-l] Sort and aggregate nodes help
To: clug-l at spss.com<mailto:clug-l at spss.com>
Message-ID:
    <9b14e15b0804291449q58b8f265v705b1a71acb9b8cc at mail.gmail.com<mailto:9b14e15b0804291449q58b8f265v705b1a71acb9b8cc at mail.gmail.com>>
Content-Type: text/plain; charset="iso-8859-1"

Hi.

My copy of SPSS Base has stopped talking to my database server, so I'm now
using Clem 12 to work directly with my database files.

I have sort/aggregate syntax for SPSS that enables me to get the amount of
the earliest gift and date of largest gift (two separate vars) made by each
donor in my gift file.  It looks like this:

For amount of first gift:

Sort cases by DCACID (a) DCGAMT (a) DCTDAT (a).
Aggregate outfile=*
/break=DCACID
/date amount=first(DCTDAT DCGAMT).

And slightly different on the sort for earliest date of largest gift:

Sort cases by DCACID (a) DCGAMT (d) DCTDAT (a).
Aggregate outfile=*
/break=DCAID
/date amount=first(DCTDAT DCGAMT).

I'm having trouble using the Sort and Aggregate nodes to reproduce this in
Clem.  Maybe it's easiest to simply use the syntax?  Still, if there's a way
to do it from within existing nodes, I'd appreciate it if someone could
point me in the right direction.

Thanks!

Mark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cammlist1.spss.com/pipermail/clug-l/attachments/20080429/84abe9b8/attachment-0001.html

------------------------------

_______________________________________________
Clug-l mailing list
Clug-l at spss.com<mailto:Clug-l at spss.com>
http://cammlist1.spss.com/mailman/listinfo/clug-l

End of Clug-l Digest, Vol 27, Issue 7
*************************************

_______________________________________________
Clug-l mailing list
Clug-l at spss.com<mailto:Clug-l at spss.com>
http://cammlist1.spss.com/mailman/listinfo/clug-l

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cammlist1.spss.com/pipermail/clug-l/attachments/20080506/6f6292e8/attachment-0001.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mark palmberg.zip
Type: application/x-zip-compressed
Size: 3503 bytes
Desc: mark palmberg.zip
Url : http://cammlist1.spss.com/pipermail/clug-l/attachments/20080506/6f6292e8/markpalmberg-0001.bin