quote:

TABLE FILE CAR
SUM
DCOST AS ''
BY COUNTRY
ACROSS SEATS NOPRINT
ACROSS CAR
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END

switch the across statement?

you have to imagine it as a horizontal BY

If you use ASNAMES, the column names in the hold file will be replaced with the contents of the very first across field. In this case SEATS.
What you want in your live situation is probably something like a month overview, where you do not want the month to be sorted by name but rather by value, but you want to see the name. That can be done, but it is a bit awkward and requires thinking out-of-the-box so to speak.
Consider the follwing example, based on the car file. It shows the dealer cost, sorted by country, across car (which in its turn is sorted by seats - noprint).

 
SET ASNAMES=ON

DEFINE FILE CAR
SORT/A19 = FTOA(SEATS,'(D3)','A3') || CAR;
END

TABLE FILE CAR
SUM
DCOST
BY COUNTRY
ACROSS SORT
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END

CHECK FILE ACRSHOLD HOLD

DEFINE FILE HOLD
COLNAME/A50 = '''' | EDIT(FIELDNAME,'9999999999999999999') | ''' AS ''' | EDIT(FIELDNAME,'$$$$$$9999999999999999') | '''';
END

TABLE FILE HOLD
PRINT COLNAME
WHERE FIELDNAME LIKE 'DEA%';
ON TABLE HOLD
END
-RUN

TABLE FILE ACRSHOLD
PRINT 
-INCLUDE hold.ftm
BY COUNTRY
END

-RUN
 

Hope this points you in the desired direction...

GamP,

You're right on. All I need to do is convert SEATS to alpha, and Susannah's CAR example will work like a charm.

I just don't understand why numeric field behave different from alpha field. Hope someone have the explaination for this.

Thanks again for answering nubi's question.

Hua

quote:

i just don't understand why numeric field behave different from alpha field. Hope someone have the explaination for this.

i leave that as an excersize for you to find out, but you may wanna think about how you would alphabetically sort numbers, or more importantly how you would put the letters A-Z in numeric order..

quote:

Originally posted by Hua:
Thanks again for answering nubi's question.
Hua

que?

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

nubi,

I am very much appreciate your challenge. If someone already gone through burning their brain cells I would be gratefully take their advices. Meanwhile, I'll do my own search too. I am quite satisfied with the workaround here. I am pointing it out so someone else don't have to scratch their head like I did in case they want to sort the numeric value first.

In regards to how and why I sort the data, it is obvious to me that the database is designed to have internal chronicle date sequence along with external representation by conventions. yyyymmdd is internal and mm/dd/yy is external, I have both fields in the file. I obviously want to sort yyyymmdd and display mm/dd/yy(the American standard).

Hua

If you have (or define) a "smart date" field for the period (e.g., format Q.YY or MtYY) you can use it for Hold Across -- the periods will appear in natural order, and the field suffix in the Hold file will be the unadorned numeric form of the data. Try this out:

SET ASNAMES=ON,ONLINE-FMT=PDF,COMPOUND=OPEN
DEFINE FILE CAR
 PERIOD/Q.YY WITH COUNTRY= IF LAST PERIOD EQ 0 THEN '2008 Q4' ELSE LAST PERIOD + 1;
END
TABLE FILE CAR  SUM SEATS  ACROSS PERIOD
END
TABLE FILE CAR  SUM SEATS  ACROSS PERIOD   ON TABLE HOLD AS STATS
END
SET COMPOUND=CLOSE
TABLEF FILE STATS PRINT *
END

it wasn't a trick question or even a particularly coding related question- you cant sort letters numerically because they are not numbers, you can sort numbers alphabetically but they wont be sorted in numerical order so will come out as 1,10,2,3,4,5,6,7,8,9

also how would you carry out arithemtic on alphanumerica characters?

Jack, I see where you want to steel me to(and I thought maybe that was what nubi have been rolling his eyes convincing me to). I should be able to move on now. And you are ahead of me when I wondered how can I format the output if I can only do PRINT *. Thanks.

nubi, neither English nor WebFOCUS are my first languages and my books had returned to the professors long time ago. I really feel dumber than I thought. So I beg you! give me an illustration, like the report elements, huh?

Hua

sorry Hua, i for some reason assumed you were American, and i wasn't rolling my eyes- look again

here is a rough example, im using the DEFINE to create a new field which is an ALPHA version of SALES.

i then run the CAR file twice, the first time summarising the Alpha version of SALES, the second time summarising the Number version of SALES.

notice that when SALES is converted to alpha and the summarised it comes out as 0 but when the number version of the field is summarised it comes out correctly as 208420?

DEFINE FILE CAR
SALES_ALPHA/A5 =SUBSTR(7, EDIT(SALES), 2, 7, 5, 'A5');
END

TABLE FILE CAR
SUM SALES_ALPHA 
ON TABLE SET STYLE *
END

TABLE FILE CAR
SUM SALES 
ON TABLE SET STYLE *
END 

nubi, I prefer to say 'huh' more than 'eh' doesn't make me an American.
Your example is 100% tricky to me, I haven't have the imagination to apply your technique to my code. But I got enough info for me to do my own homework. Thanks.
(you may now)

i suppose now that i have no idea what your talking about that a is appropriate

It is OK. Let's leave it like that. Maybe some day someone or something hit me on the head will open up my mind.

Cheers.

dont be too hard on yourself, Rome wasn't built in a day....