the first three columns i've got working, i simply did the following:
TABLE FILE My_Cars
SUM
CNT.MODEL
WEIGHT
BY MANUFACTURER
the hard part is, how can i get the "types" column to list all the distinct values in that column that correspond to that manufacturer? in addition, how can i find the "heaviest 2D vehicle", and the total weight for 4D cars?
a) is there any way to do this, and b) is there any way to do this with only one query -- not making and reading a bunch of hold files? the data source i'm accessing is huge and has a lot of security and overhead on it and it is very slow to get even a single result back. the fewer trips i have to make to the data source the better.
thanks very much!This message has been edited. Last edited by: GreenspanDan,
--------------------- WebFOCUS 7.6
July 14, 2006, 07:57 PM
dhagen
I am sure - hopeful really - that someone else will show you a better way, but in case no one does:
DEFINE FILE CAR
DOORS/A3 = IF MODEL CONTAINS '4 DOOR' THEN '4DR' ELSE '2DR';
END
TABLE FILE CAR
SUM
CNT.WEIGHT NOPRINT
BY CAR
PRINT
COMPUTE C_NOM/I5 = IF CAR EQ LAST CAR THEN LAST C_NOM + 1 ELSE 1; AS '# OF MODELS'
COMPUTE C_WEIGHT/I11 = IF (CAR EQ LAST CAR) THEN LAST C_WEIGHT + WEIGHT ELSE WEIGHT; AS 'TOTAL WEIGHT'
COMPUTE C_TYPE/A50 = IF (CAR EQ LAST CAR) AND (LAST C_TYPE NOT CONTAINS DOORS) THEN SUBSTR(50, LAST C_TYPE || ',' || DOORS, 1, 50, 50, 'A50') ELSE
IF (CAR EQ LAST CAR) THEN LAST C_TYPE ELSE DOORS; AS 'TYPES'
COMPUTE C_2DR /I11 = IF (CAR EQ LAST CAR) AND (DOORS EQ '2DR') AND (WEIGHT GT LAST C_2DR) THEN WEIGHT ELSE
IF (CAR EQ LAST CAR) THEN LAST C_2DR ELSE
IF DOORS EQ '2DR' THEN WEIGHT ELSE 0; AS 'HEAVIEST 2D'
COMPUTE C_4DR /I11 = IF (CAR EQ LAST CAR) AND (DOORS EQ '4DR') AND (WEIGHT GT LAST C_4DR) THEN WEIGHT ELSE
IF (CAR EQ LAST CAR) THEN LAST C_4DR ELSE
IF DOORS EQ '4DR' THEN WEIGHT ELSE 0; AS 'HEAVIEST 4D'
BY CAR
BY DOORS NOPRINT
WHERE TOTAL CNT.WEIGHT EQ C_NOM;
END