TABLE FILE CAR SUM BODYTYPE BY COUNTRY BY BODYTYPE ON TABLE HOLD END
TABLE FILE HOLD SUM CNT.BODYTYPE AS '' BY COUNTRY AS '' SUM BODYTYPE AS '' BY COUNTRY ACROSS BODYTYPE NOPRINT ON TABLE SET STYLE * TYPE=REPORT, GRID=OFF, $ END
gives this as a result:
ENGLAND 3 CONVERTIBLE . HARDTOP . SEDAN FRANCE 1 . . . . SEDAN ITALY 3 . COUPE . ROADSTER SEDAN JAPAN 1 . . . . SEDAN W GERMANY 1 . . . . SEDAN
Is there a nice clean way to get this as a result:
ENGLAND 3 CONVERTIBLE HARDTOP SEDAN FRANCE 1 SEDAN ITALY 3 COUPE ROADSTER SEDAN JAPAN 1 SEDAN W GERMANY 1 SEDAN
?
Thanks.This message has been edited. Last edited by: <Maryellen>,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 02, 2005, 07:48 PM
ET
brute force
DEFINE FILE CAR B1/A12=IF BODYTYPE EQ 'CONVERTIBLE' THEN BODYTYPE ELSE ' '; B2/A12=IF BODYTYPE EQ 'COUPE' THEN BODYTYPE ELSE ' '; B3/A12=IF BODYTYPE EQ 'HARDTOP' THEN BODYTYPE ELSE ' '; B4/A12=IF BODYTYPE EQ 'ROADSTER' THEN BODYTYPE ELSE ' '; B5/A12=IF BODYTYPE EQ 'SEDAN' THEN BODYTYPE ELSE ' '; CNTR/I1= IF BODYTYPE EQ LAST BODYTYPE THEN 0 ELSE 1; END TABLE FILE CAR SUM MAX.B1 MAX.B2 MAX.B3 MAX.B4 MAX.B5 BY COUNTRY ON TABLE HOLD END DEFINE FILE HOLD C1/I1=IF B1 NE ' ' THEN 1 ELSE 0; C2/I1=IF B2 NE ' ' THEN 1 ELSE 0; C3/I1=IF B3 NE ' ' THEN 1 ELSE 0; C4/I1=IF B4 NE ' ' THEN 1 ELSE 0; C5/I1=IF B5 NE ' ' THEN 1 ELSE 0; CT/I2=C1 + C2 + C3 + C4 + C5; OUT1/A64=B1||(' '|B2)||(' '|B3)||(' '|B4)||(' '|B5); OUT2/A64=LJUST(64,OUT1,'A64'); END TABLEF FILE HOLD PRINT COUNTRY AS '' CT AS '' OUT2 AS '' END
ENGLAND 3 CONVERTIBLE HARDTOP SEDAN FRANCE 1 SEDAN ITALY 3 COUPE ROADSTER SEDAN JAPAN 1 SEDAN W GERMANY 1 SEDAN
September 02, 2005, 07:51 PM
Francis Mariani
Thanks for the solution. Unfortunately, the file I want to do this on has a lot of rows, so it may not be practical. Perhaps I can get DM to do the work.
Thank you.
September 02, 2005, 08:18 PM
susannah
SET HOLDLIST PRINTONLY SET ASNAMES=ON TABLE FILE CAR BY COUNTRY BY BODYTYPE ON TABLE HOLD END TABLE FILE HOLD COUNT BODYTYPE AS NUM BY COUNTRY LIST BODYTYPE BY COUNTRY ON TABLE HOLD END TABLE FILE HOLD SUM BODYTYPE ACROSS LIST BY COUNTRY BY NUM END
September 02, 2005, 08:45 PM
Leah
Is this what you want??
TABLE FILE CAR COUNT BODYTYPE BY COUNTRY BY BODYTYPE ON TABLE HOLD AS HOLD1 END
TABLE FILE HOLD1 PRINT COMPUTE RCOUNT/I4 = IF COUNTRY EQ LAST COUNTRY AND BODYTYPE EQ LAST BODYTYPE THEN 1 ELSE IF COUNTRY NE LAST COUNTRY THEN 1 ELSE RCOUNT + 1; BY COUNTRY BY BODYTYPE ON TABLE HOLD AS HOLD2 END TABLE FILE HOLD2 SUM CNT.BODYTYPE BY COUNTRY SUM BODYTYPE AS '' ACROSS RCOUNT NOPRINT BY COUNTRY ON TABLE SET STYLEMODE FIXED ON TABLE SET NODATA ' ' END PAGE 1
BODYTYPE COUNTRY COUNT ---------------------------------------------------------------- ENGLAND 3 CONVERTIBLE HARDTOP SEDAN FRANCE 1 SEDAN ITALY 3 COUPE ROADSTER SEDAN JAPAN 1 SEDAN W GERMANY 1 SEDAN
September 02, 2005, 08:51 PM
Leah
I like your solution, Susannah, I keep forgetting about list.
September 06, 2005, 02:25 PM
Francis Mariani
Susannah, thanks for the example, it works for me.