I'm extracting data for students for three years, however, my client only wants certain selected data from the subsequent 2nd and 3rd lines to show in the same line as the first line, something like this:

Row 1: Field1a, Field1b, Field1c, Field1d Field1e
Row 2: Field2a, Field2b, Field2c, Field2d Field2e
Row 3: Field3a, Field3b, Field3c, Field3d Field3e

What my client wants is this:

Row 1: Field1a, Field1b, Field1c, Field1d Field1e, Field2d, Field2e, Field3d, Field3e

I'm guessing I would do some sort of ACROSS an dBY but can't quite figure that one out.

If anyone is willing to help me out, I'd love to hear from you. Thanks!

This message has been edited. Last edited by: Kerry,

quote:

Field1a, Field1b, Field1c, Field1d Field1e, Field2d, Field2e, Field3d, Field3e

something like this

DEFINE FILE ...
FIELD1a/A20=IF YEAR EQ 'YEAR1' THEN FIELD1a ELSE ' ';
FIELD1b/A20=IF YEAR EQ 'YEAR1' THEN FIELD1b ELSE ' ';
FIELD1c/A20=IF YEAR EQ 'YEAR1' THEN FIELD1c ELSE ' ';
FIELD2d/A20=IF YEAR EQ 'YEAR2' THEN FIELD2d ELSE ' ';
FIELD2e/A20=IF YEAR EQ 'YEAR2' THEN FIELD2e ELSE ' ';
FIELD3d/A20=IF YEAR EQ 'YEAR3' THEN FIELD3d ELSE ' ';
FIELD3e/A20=IF YEAR EQ 'YEAR3' THEN FIELD3e ELSE ' ';
END
TABLE FILE ...
SUM MAX.FIELD1a
MAX.FIELD1b
MAX.FIELD1c
MAX.FIELD2d
MAX.FIELD2e
MAX.FIELD3d
MAX.FIELD3e
BY STUDENT
END

WM,

I'm not going to test this so use at your own risk. And you'll have to tweak it for your personal application.

First make sure that your data is sorted by student id then by year.

DEFINE FILE filename
CNTR1/I2=IF STUDENT NE LAST STUDENT THEN CNTR1+1 ELSE CNTR1;
CNTR2/I2=IF STUDENT NE LAST STUDENT THEN 1 ELSE 
        IF YEAR NE LAST YEAR THEN CNTR2+1 ELSE CNTR2;

COL1/A5=IF CNTR2 EQ 1 THEN FLD1A ELSE IF CNTR2 EQ 2 THEN FLD2D ELSE FLD3D; 
COL2/A5=IF CNTR2 EQ 1 THEN FLD1B ELSE IF CNTR2 EQ 2 THEN FLD2E ELSE FLD3E;
COL3/A5=IF CNTR2 EQ 1 THEN FLD1C ELSE IF CNTR2 EQ 2 THEN ' ' ELSE ' '; 
COL4/A5=IF CNTR2 EQ 1 THEN FLD1D ELSE IF CNTR2 EQ 2 THEN ' ' ELSE ' ';
COL5/A5=IF CNTR2 EQ 1 THEN FLD1E ELSE IF CNTR2 EQ 2 THEN ' ' ELSE ' ';
END
TABLE FILE filename
SUM COL1 COL2 COL3 COL4 COL4
BY CNTR1 NOPRINT
BY STUDENT
ACROSS CNTR2
END

Now you could also do the across by year. And you would have to figure out how not to print cols 3-5 for the 2nd and 3rd years but maybe this architecture would work for you.

JG and Ginny,

Thank you both so very much! I was close but still off a bit. I'll use your suggestions and this should help greatly.

Thanks for the fast replies!

I'm curious as to why the first two fields are not needed ?
Are the values in these fields :

 
Row 2: Field2a, Field2b
Row 3: Field3a, Field3b

... the same as in Row 1 ?

 
Row 1: Field1a, Field1b

In othe words, does the segment TABLE contain redundant data ?
From what I understand about segments, they can contain repeated data that was extracted from the source tables in the DBMS.

For example :

 
PROD_ID, PROD_CAT, PRICE, COST, QTY  

... where the same item can have different PRICE, COST and QTY on different rows of the data set (in a sales or transactions data set) ?

Is the TABLE you are using do an "EXTRACT" of data that was stored in separate tables in the source DB,
and JOINed data from normalized data in the source DB ?

I thought that a FOCUS TABLE was similar to an SQL "table," but I see a lot of segmented TABLEs that contain redundant data.

Apparently WF "TABLEs" use redundant data to avoid JOINs (by doing the JOINs in the RDBMS), and to speed up processing ?

So, I'm thinking that's why webmeister wants to eliminate the fields from subsequent records ?
Is this a common occurrence ?

quote:

my client only wants certain selected data from the subsequent 2nd and 3rd lines to show in the same line as the first line

It is a requirement of his customer.

Thanks Ginny,
I wasn't really second-guessing his requirements.

I was just wondering WHY redundant data appears in a WF TABLE ?

A database is supposed to remove redundant data, but it seems to happen a lot.

I hope my question doesn't confuse the origianl issue !

Well, Charles, now you are getting into data base design issues. Relational tables by definition tend to be 'normalized' which means that they don't contain redundant data except for foreign keys.

FOCUS data bases are hierarchical and unless designed that way, don't contain redundant data. Linkages to subordinate segments is done via an index model. The parent 'points' to the child.

Maybe you could give an example of what you mean by "WF TABLE" and "redundant".

Right : "database design issues" PLUS how WebFOCUS trades off redundancy for improved processing time, since the JOINs have already been done, but repeat some data in doing so.

By "WF TABLE" I mean the data set that is accessed with a "TABLE FILE" request in WebFOCUS.

I've seen "extracts" that JOIN data from separate tables in a normalized database, and generate another "view" that could contain redundant data.

The following layout describes the results of JOINing the two tables as a "view"

Fred Jones and John Smith appear only once in the remote CLIENT table (with the CUSTID primary key), and each record/row in the TRANS table contains purchase data with the CUSTID (foreign key), that "points" to the related CLIENT record.

CLIENT table :

ClIENT data                  
---------------------------- 
CUSTID LASTNAME FIRSTNAME     
------ -------- ------------ 
ID1234 Jones    Fred         
ID1111 Smith    John         
...

TRANS table :

TRANS data
----------------------------------
CUSTPK DESCRIPTION         PRICE  
------ ------------------- -------
ID1234 XYZ123 Gasket          1.95 
ID1234 ABC456 Wrench          9.99
ID1234 XXX333 Socket set     19.99
ID1234 XYZ123 Gasket          1.95
ID1234 ABC456 Wrench          9.99
ID1234 XXX333 Socket set     19.99
...

JOINing these two tables would produce this WebFOCUS "TABLE" :

CLIENT_DATA 
---------------------------- ----------------------------------
CUSTID LASTNAME FIRSTNAME    DESCRIPTION         PRICE  
------ -------- ------------ ------------------- -------
ID1234 Jones    Fred         XYZ123 Gasket          1.95 
ID1234 Jones    Fred         ABC456 Wrench          9.99
ID1234 Jones    Fred         XXX333 Socket set     19.99
ID1111 Smith    John         XYZ123 Gasket          1.95
ID1111 Smith    John         ABC456 Wrench          9.99
ID1111 Smith    John         XXX333 Socket set     19.99
...

The FOCUS .mas file would then describe this with the CLIENT field descriptions in the PARENT segment, and the TRANS field descriptions in a 'child' segment ?
(NOTE : This is only one way to bring it into WebFOCUS.
It could have generated separate CLIENT and TRANS table segments,
that would need to be JOINed with WebFOCUS code.)

So, the FOCUS data set now has redundant data in it, and this TABLE FILE request :

TABLE FILE CLIENT_DATA
   PRINT *
END

would display the CLIENT's data for each TRANS record that matches (CUSTID = CUSTFK) :

CUSTID LASTNAME FIRSTNAME     DESCRIPTION           PRICE  
------ -------- ---------     -----------           -----  
ID1234 Jones    Fred          XYZ123 Gasket          1.95 
ID1234 Jones    Fred          ABC456 Wrench          9.99
ID1234 Jones    Fred          XXX333 Socket set     19.99
ID1111 Smith    John          XYZ123 Gasket          1.95
ID1111 Smith    John          ABC456 Wrench          9.99
ID1111 Smith    John          XXX333 Socket set     19.99
...

This could be done as a tradeoff between storage space and processing time ?

This could also be the reason for webmeister’s need to eliminate those fields from the report, since they are redundant data ?

You’re right, Ginny, that was complicated !

Thanks for listening,

Ginny,

Thanks for helping to explain to Charlz what the intent is.

Charlz,
Even though each student can have up top three rows of data, the data is by no means redundant. In this case, the three rows represent three years of data and the client (as Ginny stated, this is a client request) and all the client wants to see for the 2nd and 3rd year are only certain pieces of data.

For example, A student might pay the same tuition for the 3 years, so why would the client want to see that three times? However, the student can have different GPA's over the three years, so the client would want to see that.

The overall request and effort do not necessarily concern themselves with redundancy or repetition.... our databases are set up to have quite strong normalcy. Repetition of data would show up immediately in the case of a report if there was any, and of course would be corrected.

Thanks for replying.....

Charles,

You might want to start a new post on these questions so that WM can proceed with getting an answer to his question.

But a short answer to your question is that the result of a request against a joined structure is either a flat file or report, no longer a data base. For uniqueness all keys must be specified.

And to not show repeated key values on the report, simply do a BY on them.

Hope this helps.

I will start a new post on this so as not to confuse the issue here !

Thanks for the suggestion, Ginny ;>}

This message has been edited. Last edited by: Charlz,