Re: Can someone help me with my Query/400 quest? (yes, I know, it's prehistoric ;)

[Booth Martin <booth@xxxxxxxxxxxx>]

I would explore using a two step process if for no other reason than 
clarity to myself.

First step: using query/400, I would create and populate an interim file 
laid out with SYMBOL,JULIANDATE, VALUE, and LASTVALUE.  LASTVALUE would 
be a result field which is the saved VALUE from the previous record (or 
0 if there is a change in SYMBOL).

Second step: straight forward Query/400 report from the interim file.

I say JULIANDATE, but that may well be irrelevant.  It isn't clear to me 
how to handle weekends, etc.

Here is a link that seems to me to be of interest: 
http://publib.boulder.ibm.com/html/as400/v4r5/ic2931/info/db2/rbaf9mst78.htm



On 2/9/2015 9:53 AM, Rene van der Kraats wrote:
>     Hi All,
>
>     I hate to ask for help with this as I'm trying to discover as much as
>     possible myself, but I'm afraid I'm a bit stuck here.
>
>     I'm dabbling with Query/400 (just for entertainment, mind you) but, never
>     having used this program before, it's a bit hard to get into the right
>     'mind-set'.
>
>     I have these database files with a layout similar to this:
>
>     DATE SYMBOL VALUE
>     01/01/2015 AAA 50
>     01/01/2015 BBB 40
>     01/01/2015 CCC 20
>     01/02/2015 AAA 60
>     01/02/2015 BBB 35
>     01/02/2015 CCC 25
>     01/03/2015 AAA 40
>     01/03/2015 BBB 25
>     01/03/2015 CCC 23
>
>     etc...
>
>     I'm trying to produce a query where it calculates the delta between
>     similar symbols, per day.
>
>     So:
>
>     01/01/2015 AAA = 50
>     01/02/2015 AAA = 60 -> delta compared to previous day = 10
>     01/03/2015 AAA = 40 -> delta compared to previous day = 20
>
>     ...and so on, for each symbol.
>
>     I think I need to build some query result fields roughly like 'delta =
>     abs(VALUE(current date) - VALUE(current date - 1))' (I hope this makes
>     sense), but I cannot figure out how to do this.
>     I've read a boat-load of PDF's, but none really shows me how to approach
>     this problem (or perhaps I just don't 'get it' :)
>
>     Can someone perhaps push me in the right direction? I know this probably
>     can be solved much easier and quicker with other tools, but out of
>     curiosity I'd still like to know how this is accomplished in Query/400 :)
>
>     Regards,
>
>     Rene.