|
Dynamic Result Field in Query/400
Hey, Ted:
Every month we run a query that reports year-to-date
sales. The year-to-date sales field, YTD$, is defined
as result field that adds up monthly numeric buckets
from a sales detail file. For example, the YTD$ for
March will contain the expression Jan+Feb+Mar. However,
before we can run this query each month, we have to
add the next month to the expression. We would like
to avoid having to modify the expression every month.
Is there a way to make this query read the expression
from a database file?
-- Bill
The idea of importing an expression from a file is
interesting, Bill, but Query/400 cannot handle it.
What I have seen in the past is that most people zero
out all the buckets at the beginning of the year and
add all of them together to get YTD. The problem with
that approach is that you can only get current YTD.
You can't get February YTD in the middle of June.
Here's another way to solve your problem.
Create a file with a one-digit field for each bucket.
Since you mentioned month names, I'll assume you use
a twelve-period accounting year. If so, you could
call the fields S1 through S12.
Put one record into the file--no more, no less. Put
a zero (0) in a month's field if you do not want to
include that month in the sum, and put a number one
(1) in the field if you do want to include it in the
sum. Therefore, to sum up January, February, and
March, the values are 1, 1, 1, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0.
In Query, add the one-record selection file to the
list of files. The join can be a little tricky,
because you have to join the one record in your
selection file to all of the records produced by
the query. This type of join is called a cross-join
or Cartesian product. Here are some ways to do that.
If the sales file and the one-record selection file
are the only files your query processes, specify 1
(Matched records) on the Specify Type of Join panel.
When you press Enter, Query presents the Specify How
to Join Files panel. Key the special value *ALL into
the first blank on the panel. It's at line 7, column
2, and is the top blank in the left Field column.
If there are more than two files involved, you will
have to trick the join in some way. One way that
usually works is to add a character field to the
one-record selection file, fill it full of garbage
that can't possibly match anything else in your
database, and join it to any alpha field in another
file on an NE (not equal) condition.
Another way that works if the join type is 1 (Matched
records) is to use the *ALL special value technique
and move the join criteria for the other files to the
Select records panel.
I'll assume your bucket fields are called AMT1 thru
AMT12.
Code your expression for the YTD$ field like this:
AMT1*S1+AMT2*S2+ ... + AMT12*S12
Query multiplies the buckets against the fields in
the selection file. Each multiplication produces the
value that is in the bucket field or zero.
Query only gives you three short lines for the expression,
so you may not have enough room for the expression.
If so, create one field to add up the first six months,
create another field to sum up the last six months,
and add the two together to get the year-to-date field.
It depends on how long your field names are.
-- Ted
|
