How to reorganize a table? 1

[kaeserea]

Hi folks!

I have a db2 table as source for my webfocus report. Unfortunately the table's organisation does not really fit with what I have to display. I have to order data by fields that do not exist and cannot be created by DEFINE. I figured a way out but don't know how to do it.

Here's the example. It is about telephone calls. The db2 table looks like this:
[tt]
company weekday answered_calls not_answered_calls
------- ------- -------------- ------------------
a Mon 987 123
b Tue 1034 50
a Thu 543 100

Yet the table should look like this so that I can make my report:

company weekday number type_of_call
------- ------- ------ ------------
a Mon 987 answered
a Mon 123 not_answered
b Tue 1034 anserwed
b Tue 50 not_answered
a Thu 543 answered
a Thu 100 not_answered
[/tt]
Can anybody help?
Thanx
Eva

 

[FrankExp]

Hi Eva,

How about this:

DEFINE FILE filename
  ANSCALLS/I9  = ANSWERED_CALLS;
  ANSWERED/A20 = 'answered';
  NOT_ANSCALLS/I9  = NOT_ANSWERED_CALLS;
  NOT_ANSWERED/A20 = 'not_answered';
END

MATCH FILE filename
  SUM   ANSCALLS 
     BY COMPANY
     BY WEEKDAY
     BY ANSWERED AS TYPE_OF_CALL
RUN
      FILE filename
  SUM   NOT_ANSCALLS
     BY COMPANY
     BY WEEKDAY
     BY NOT_ANSWERED AS TYPE_OF_CALL
  AFTER MATCH HOLD OLD-AND-NEW
END

DEFINE FILE HOLD
  NUMBER/I9 = IF TYPE_OF_CALL EQ 'answered'
              THEN ANSCALLS
              ELSE NOT_ANSCALLS;
END

TABLE FILE HOLD
  PRINT NUMBER
        TYPE_OF_CALL
     BY COMPANY
     BY WEEKDAY
     BY TYPE_OF_CALL NOPRINT
END

This should do the trick. Good luck! Frank van de Kant

http://www.explicit.nl

 

[kaeserea]

Hello Frank,

Thanks a lot. It works! Now I even have a better understanding of what MATCH does.

Yet there is one mistake in your MATCH statement. It should be OLD-OR-NEW not OLD-AND-NEW.

Best wishes
Eva

 

[FrankExp]

Eva,

You're right. Slip of the keyboard.

Frank van de Kant

http://www.explicit.nl

 

[kaeserea]

Hi Frank,

I'd like to come back to this thread on which we communicated some time ago. The report works fine with this MATCH method. I even used this trick to make a column title which streches across four columns (these I created with ACROSS in the actual report). Works all fine with HTML output. But with PDF output this is what happens: the columns title which should be across four other columns is only above the first ACROSS column. As a result the page width is too small and the report is shown on two pages which I have to print and lay beside eacht other.

Is there a way to prevent this? To make the column title strech across 4 columns in the PDF output as well?

Best wishes
Eva

 

[focwizard]

Another option to consider, is to treat the data as if it was in an OCCURS segment.

Your layout looks like this:

company weekday answered_calls not_answered_calls

So, if you treat the fields answered_calls and not_answered_calls as if they were 2 instances of a field in its own segment, which occurs twice (you do this with an additional segment in the Master, and the keyword POSITION to reposition you back at those fields), you can use the artificial field ORDER to number the instances 1 and 2. Now it becomes a single pass of the data, and very straight-forward, with 1 decoding to 'answered', and 2 decoding to 'not answered'