I have some date in this format:

CUSTOMER	SALES	COST
XYZ	1000	600
ABC	2500	1800
 

I want to show it like this:

CUSTOMER		
XYZ	SALES	1000
XYZ	COST	600
ABC	SALES	2500
ABC	COST	1800
  

So, I am using the OVER functions as follows:

quote:

SUM SALES OVER
COST
BY CUSTOMER

But the result is missing to show CUSTOMER in all rows, so it prints like this:

CUSTOMER		
XYZ	SALES	1000
	COST	600
ABC	SALES	2500
	COST	1800
  

Do you have any ideas how I could accomplish this?

Thanks in advance,

Rafael Rangel

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

Sounds like you're looking for the BYDISPLAY KEYWORD.

It's not that. I already had the option set:

ON TABLE SET BYDISPLAY ON 

Any other ideas?

By "ACCOUNT", do you mean "CUSTOMER" (ABC, XYZ)?
I'm not particularly proud of this one. But, try it on for size:

TABLE FILE CAR
SUM CAR AS ''
    SALES AS 'SALES' OVER
    CAR AS ''
    RCOST AS 'COST' 
BY CAR NOPRINT
ON TABLE SET BYDISPLAY ON 
END

Yes.

-* CREATE SAMPLE DATA
TABLE FILE CAR
PRINT COUNTRY
AND COMPUTE
LIST/I5=LIST+1;
CUST/A12 = DECODE LIST(1 XYZ 2 ABC ELSE '');
SALES/D12=DECODE LIST(1 1000 2 600 ELSE 00);
COST /D12=DECODE LIST(1 2500 2 1800 ELSE 00);
IF TOTAL CUST NE ' '
ON TABLE HOLD AS DATA
END

-* Make sales and cost into separate records
DEFINE FILE DATA
TYPE1/A10='SALES';
TYPE2/A10='COST';
END

MATCH FILE DATA
BY CUST
BY TYPE1 AS TYPE
BY SALES AS VALUE
RUN
FILE DATA
BY CUST
BY TYPE2 AS TYPE
BY COST AS VALUE
AFTER MATCH HOLD OLD-OR-NEW
END

-* LIST
TABLE FILE HOLD 
PRINT VALUE 
BY HIGHEST CUST 
BY HIGHEST TYPE
ON TABLE SET BYDISPLAY ON 
END

Hi Rafa

TABLE FILE CAR
PRINT SALES
BY CUSTOMER
ON CUSTOMER SUBFOOT
CUSTOMER COST
...
TYPE=SUBFOOT, HEADALIGN=BODY,$

Try that and not using the over will give you more control over formatting

Rafa,

Using a bit of FOCUS trickery:

  
-* File rafa1.fex
-* Create titles
-*
DEFINE FILE CAR
TRCOST/A12='RETAIL';
TDCOST/A12='DEALER';
END
-* Create a SAVE file containing data and titles
-* Note taking advantage that RCOST and DCOST have the same format. If not use the DEFINE above to make them the same
-*
TABLE FILE CAR
PRINT TRCOST RCOST TDCOST DCOST
BY CAR
ON TABLE HOLD AS RAFA FORMAT ALPHA
END
-*
-* Create a Master for RAFA. 
-* Note that title and data are defined once with multiple occurences
-*
EX -LINES 7 EDAPUT MASTER,RAFA,C,MEM
 FILENAME=RAFA    , SUFFIX=FIX
 SEGMENT=RAFA, SEGTYPE=S0
 FIELDNAME=CAR,  ALIAS=CAR,  USAGE=A16, ACTUAL=A16, $
 SEGMENT=COST, PARENT= RAFA, SEGTYPE=S0, OCCURS=VARIABLE
 FIELDNAME=TT,   ALIAS=TT,   USAGE=A12, ACTUAL=A12, $
 FIELDNAME=COST, ALIAS=COST, USAGE=D7,  ACTUAL=A07, $
-RUN
-*
-* Output
-*
TABLE FILE RAFA
PRINT CAR
TT AS ''
COST AS ''
END

I followed Jack Gross' suggestion of using a MATCH on a hold file and it worked beautifully!

Daniel, your trickery was a bit too much for me, due to my lack of experience. But it is a clever way to go around it.

Thank you everybody for the suggestions.

Rafa

Rafa,

This technique is called "the alternate master". It can be very useful. Your example is a relatively simple one.
to learn a bit more about this, I suggest you look at the generated RAFA file. For this, you can comment out the ON TABLE HOLD AS RAFA statement. Insert a ?FF RAFA after the end and insert a -EXIT after the TABLE request. You will then see the generated file. By using "view source" on the output, you can see the original RAFA master and compare it to the one created in the fex.