×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

ROW_TOTAL: can I insert a computation within it?

ROW_TOTAL: can I insert a computation within it?

ROW_TOTAL: can I insert a computation within it?

(OP)
Hi!

I need to generate a report about telephone calls which should look like this:

incoming_calls       outgoing_calls       total
--------------       --------------       -----
number  av_duration  number  av_duration  number av_duration
------  -----------  ------  -----------  ------ -----------
123       207         13       304         136      256

I can create the total for number by ROW-TOTAL. Yet if I use it, of course the average call duration is summed up: 207+304=511. Which is not correct. I need to compute this field by summing up the length of the calls divided by the total sum of calls.

So my question is whether it is possible the make a calculation within the ROW-TOTAL statment and if not how I can solve this problem in another way.

Thanx for help.
Eva

RE: ROW_TOTAL: can I insert a computation within it?

Eva,

Use a define:

DEFINE FILE filename
  TOTALCALLS/I9 = INNUMBER + OUTNUMBER;
  AVGTIME/I9  = ((AVGINTIME  * INNUMBER) +
                 (AVGOUTTIME * OUTNUMBER) / TOTALCALLS);
END

Frank van de Kant
http://www.explicit.nl

RE: ROW_TOTAL: can I insert a computation within it?

(OP)
Hello Frank,

I thought about that as well, but the problem is that I generate the first line (incoming_calls, outgoing_calls) with an ACROSS statement and then the DEFINE suggesstion does not work. Here is my procedure:

SUM ANSWERED_CALLS
    COMPUTE AVETIME/D12 = SUM.TIME / SUM.ANSWERED_CALLS;
ACROSS TYPE_OF_CALL
BY COMPANY

Where TYPE_OF_CALL is either incoming_calls or outgoing_calls.

Eva

RE: ROW_TOTAL: can I insert a computation within it?

If the number of Typecalls is 2 and never more you can define these as well.
Then you can build 2 different requests. The first builds detailled info, the second builds the total info. Both can produce averages and absolute values. In the end you can build one file by using a MATCH-statement.
Then you can match the records on the companynumber and weeknumber.

If you really need help I can code it for you, but then I need a little bit more time.

Frank van de Kant
http://www.explicit.nl

RE: ROW_TOTAL: can I insert a computation within it?

Hi Eva,

Have you tried inserting

ON COMPANY RECOMPUTE MULTILINES

after the BY COMPANY line? This usually works for me.
Andy

RE: ROW_TOTAL: can I insert a computation within it?

(OP)
Hello Andy,

sorry but this does not work.

Eva

RE: ROW_TOTAL: can I insert a computation within it?

(OP)
Hi!

For everybody who is interested in the solution, here it is:

These are the columns of the DB2 source:
company
call_type
day_of_week
answered_calls
not_answered_calls
total_time_of_calls

Where call_type is either e for coming from external, or i for coming from within the company.

The report should look like this:

company  calls from outside  calls from within  all calls
-------  ------------------  -----------------  -----------
          a        av_time    a        av_time   a  av_time
         ---       --------  ---       -------  --- -------
xyz      1234          146   45          79     1279    128
...       ...          ...   ..          ..      ...    ...


Where a means answered calls and av_time means the average time one call took.

The problem was to make the "all call" column which you cannot create with ROW-TOTAL, because that would means that two averyge times are added. And this is not an average time one call took regarding all calls! You need to make a compute here which is not possible within a ROW-TOTAL.

So Frank helped me out by suggesting a mixture of DEFINE and MATCH. And this works:

-* 1.) create all call types:

DEFINE FILE CTMMIN
  KOMTEXT/A15 = DECODE CALL_TYPE('e' 'calls from outside'
                                   'i' 'calls from within');
  GESKOMTEXT/A15 = 'all calls';
END

-* 2.) create a table which includes all call types

MATCH FILE CTMMIN
  SUM   ANSWERED_CALLS
        TOTAL_TIME_OF_CALLS
     BY COMPANY
     BY KOMTEXT AS TYPE
RUN
      FILE CTMMIN
  SUM   ANSWERED_CALLS
        TOTAL_TIME_OF_CALLS
     BY COMPANY
     BY GESKOMTEXT AS TYPE
 AFTER MATCH HOLD AS TYPES FORMAT FOCUS OLD-OR-NEW
END

-* 3.) create the actual report

SET NODATA = '-'

-* use TABLE FILE TYPES
-*     PRINT *
-*     END
-* to see why I need a DEFINE to clue some columns created by the HOLD together

DEFINE FILE TYPES
 ANSW/I11 = E03 + E05;
 TIME/I11 = E04 + E06;
END

TABLE FILE TYPES

SUM ANSW AS 'a'
    COMPUTE AVTIME/D12 = SUM.TIME / SUM.ANSW; AS 'av_time'
BY COMPANY
ACROSS TYPE AS ''

END


Created with much help from Frank!

Eva

RE: ROW_TOTAL: can I insert a computation within it?

Let me respond by starting with your procedure:

SUM ANSWERED_CALLS
    COMPUTE AVETIME/D12 = SUM.TIME / SUM.ANSWERED_CALLS;
ACROSS TYPE_OF_CALL
BY COMPANY

This will create THREE columns for each ACROSS value. The first is ANSWERED_CALLS. The second is SUM.TIME (NOPRINTed, since you use it in a COMPUTE but don't reference it in the request). The third is AVETIME. The columns can be refererred to by their position, as 'Cn'.

After the ACROSS (but BEFORE the BY) add the following:

COMPUTE
ANSWERED_CALLS/I9 = C1 + C4 + C7 + C10 + C13 + C16;
COMPUTE AVETIME/D12 = (C2 + C5 + C8 + C11 + C14 + C17)/ANSWERED_CALLS;

This uses COLUMN ('C') notation, to indicate which columns are used in the calculations.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close