Focal Point
[CLOSED] Grouping ACROSS columns

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6877031126

June 11, 2012, 06:42 AM
Keerthi
[CLOSED] Grouping ACROSS columns
how can we group across columns?

Required:

BMW Audi Jaguar BMW Audi Jaguar
Model DCOST DCOST DCOST RCOST RCOST RCOST
----- ------ ---- ----- ----- ----- -------
----- ------ ---- ----- ----- ----- -------


original code:

TABLE FILE CAR
SUM
DCOST
RCOST
ACROSS CAR
BY MODEL
END

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


WebFOCUS 7.6
Windows, All Outputs
June 11, 2012, 09:13 AM
GamP
It's a bit of awkward code, but it has the desired ersult for the given example.
It may of may not work for you in real life.... but here it is:
TABLE FILE CAR
SUM DCOST
COMPUTE SORT/I2 = 1;
COMPUTE SORTNAME/A5='DCOST';
BY CAR
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END
FILEDEF KEEP DISK KEEP.FTM (APPEND
TABLE FILE CAR
SUM RCOST
COMPUTE SORT/I2 = 2;
COMPUTE SORTNAME/A5='RCOST';
BY CAR
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END

TABLE FILE KEEP
SUM RE
BY MODEL
ACROSS SORT NOPRINT
ACROSS CAR
ACROSS SORTNAME AS ''
END



GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
June 11, 2012, 05:01 PM
Crymsyn
This is similar to GamP's way but uses the MORE statment instead of APPEND and the DCOST and RCOST are above the CARs.

DEFINE FILE CAR
CUR_COST/D20=DCOST;
CUR_SORT/A63='DCOST';
END
TABLE FILE CAR
SUM
CUR_COST
CUR_SORT
BY MODEL
BY CAR
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END

DEFINE FILE CAR
CUR_COST/D20=RCOST;
CUR_SORT/A63='RCOST';
END
TABLE FILE CAR
SUM
CUR_COST AS ''
ACROSS CUR_SORT AS ''
ACROSS CAR
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
MORE
FILE KEEP
END



WF: 8201, OS: Windows, Output: HTML, PDF, Excel
June 12, 2012, 07:27 AM
Danny-SRL
Or with an alternate master:
  
-SET &ECHO=ALL;
-* File keerthi01.fex
TABLE FILE CAR
SUM
COMPUTE S1/A6='DEALER';
DCOST
COMPUTE S2/A6='RETAIL';
RCOST
BY CAR
BY COUNTRY
ON TABLE HOLD AS KEERTHI FORMAT ALPHA
END
-RUN
EX -LINES 8 EDAPUT MASTER,KEERTHI,C,MEM 
FILENAME=KEERTHI , SUFFIX=FIX 
SEGMENT=KEERTHI, SEGTYPE=S0
 FIELDNAME=CAR, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
 FIELDNAME=COUNTRY, ALIAS=E02, USAGE=A10, ACTUAL=A10, $
SEGMENT=COST, PARENT=KEERTHI, OCCURS=VARIABLE
 FIELDNAME=S, ALIAS=E03, USAGE=A6, ACTUAL=A06, $
 FIELDNAME=COST, ALIAS=E04, USAGE=D7, ACTUAL=A07, $
-RUN
TABLE FILE KEERTHI
SUM COST
ACROSS S AS ''
ACROSS CAR
BY COUNTRY
END



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

June 15, 2012, 08:12 AM
Keerthi
Thanks you. That was exactly what I was looking for. but I have a small issue now. My report is actually an AHTML report and I was wondering on how we can supress the unwanted column title.

TABLE FILE KEERTHI
SUM COST AS ''
ACROSS S AS ''
ACROSS CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT AHTML
END

I tried giving a blank alias name for COST column but however it shows up as column1 , column 2..etc in the report.

Appreciate your help.

Thanks,
Keerthi


WebFOCUS 7.6
Windows, All Outputs
June 20, 2012, 08:23 AM
Keerthi
Thanks Crymsyn. One question though, is there a way to remove the extra line in the title?

DEFINE FILE CAR
CUR_COST/D20=DCOST;
CUR_SORT/A63='DCOST';
END
TABLE FILE CAR
SUM
CUR_COST
CUR_SORT
BY MODEL
BY CAR
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE HOLD AS KEEP
END

DEFINE FILE CAR
CUR_COST/D20=RCOST;
CUR_SORT/A63='RCOST';
END
TABLE FILE CAR
SUM
CUR_COST AS '&|nbsp'
ACROSS CUR_SORT AS ''
ACROSS CAR AS ''
BY MODEL
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR ';
ON TABLE PCHOLD FORMAT AHTML
MORE
FILE KEEP
END

when I use the above code, I get an extra line in column title...
any help is greatly appreciated.

Thanks,
Keerthi


WebFOCUS 7.6
Windows, All Outputs
June 20, 2012, 02:29 PM
Dan Satchell
The MacGyver technique may offer another solution:

JOIN CLEAR *
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS J1
-*
DEFINE FILE CAR
 BLANK/A1 WITH BODYTYPE = ' ';
 COSTX/D8S = IF (FSEQ.COUNTER EQ 1) THEN DEALER_COST ELSE RETAIL_COST ;
 TYPEX/A5  = IF (FSEQ.COUNTER EQ 1) THEN 'DCost' ELSE
             IF (FSEQ.COUNTER EQ 2) THEN 'RCost' ELSE '';
END
-*
TABLE FILE CAR
 SUM COSTX AS '&|nbsp;'
 BY  MODEL
 ACROSS TYPEX AS ''
 ACROSS CAR   AS ''
 WHERE CAR EQ 'AUDI' OR 'BMW' OR 'JAGUAR';
 WHERE (TYPEX NE '');
 ON TABLE COLUMN-TOTAL
 ON TABLE SET NODATA ''
 ON TABLE PCHOLD FORMAT AHTML
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
July 03, 2012, 12:12 PM
Doug
quote:
I get an extra line in column title...
Are you talking about the grid lines? If so, consider adding this:
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
GRID=OFF, BORDER=LIGHT, FONT=ARIAL, SIZE=12,$
ENDSTYLE