

Go  New  Search  Notify  Tools  Reply 
Member 
Hi All, In my report I need to sort a column which having alphanumeric values. Eg: Column1 1 lb 101  500 lbs 11  20 lbs 2 lbs 3 lbs I want sorting in order like 1 lb, 2 lbs,3 lbs,1120 lbs,101500 lbs etc. Can I anyone suggest way to do.This message has been edited. Last edited by: FP Moderator, Apps Studio 8.1.0.5 Windows 7  

Master 
If you convert Column1 to a number, and sort by that new field you'll get the result you want. Create a DEFINE and use the following syntax: New_column/I9=EDIT(COLUMN1); Note, this will error out if there is anything other than numbers in COLUMN1. Consequently, you'll have to strip out just the amount. Converting a string like 101500 or lbs will not work. The alternative is to create a new field and call it COL_ORD/I9 and DECODE each of those values to 1,2,3, et cetera. Then add BY COL_ORD NOPRINT to the beginning of the report.This message has been edited. Last edited by: BabakNYC, WebFOCUS 8201, Unix, Windows  

Member 
column1 includes alphanumeric. eg: 1 lb, 2 lbs, 3 lbs etc. Apps Studio 8.1.0.5 Windows 7  

Master 
Then you have to use DECODE COLUMN1(1 lb, 1, 2 lbs, 2....); and add BY SORT_ORD NOPRINT https://webfocusinfocenter.inf...er/source/decode.htm WebFOCUS 8201, Unix, Windows  

Member 
What about these kind of values 101  500 lbs,11  20 lbs etc. We cant give DECODE for many. Apps Studio 8.1.0.5 Windows 7  

Virtuoso 
Using DECODE, you will need to list EVERY POSSIBLE values. It could be a pain if you have a large list or "unknown/not predefined" values. Per example (doesn't have to be in wished order for DECODE, I've listed in whished order for readability): DEFINE FILE abc SORT_ORD /I2 = DECODE COLUMN1 ('1 lb' 1 '2 lbs' 2 '3 lbs' 3 '11  20 lbs' 4 '101  500 lbs' 5 ELSE 6); END TABLE FILE abc SUM ... BY SORT_ORD NOPRINT BY COLUMN1 END WF 7.7.05M, 8.1.05M, 8.2.0.1M Windows, HTML, Excel In Focus since 2007  

Member 
Here's a different solution for a limited set of values(up to 6 number places). I didn't like the decode if you had users typing in the data. If you are good with loops I'm sure it could be simplified. DATA  All text fields NUMERAL OTHER_TEXT 1.00 simple 10.00 simple double 15 oz mixed 1001000 oz mixed range 2.00 simple 1200 liters mixed 2 CODE TABLE FILE SAMPLE SAMPLE.SAMPLE.NUMERAL COMPUTE 1ST/A10 = SUBSTR(100, SAMPLE.SAMPLE.NUMERAL, 1, 2, 1, 'A10'); COMPUTE 2ND/A10 = SUBSTR(100, SAMPLE.SAMPLE.NUMERAL, 1, 3, 2, 'A10'); COMPUTE 3RD/A10 = SUBSTR(100, SAMPLE.SAMPLE.NUMERAL, 1, 4, 3, 'A10'); COMPUTE 4TH/A10 = SUBSTR(100, SAMPLE.SAMPLE.NUMERAL, 1, 5, 4, 'A10'); COMPUTE 5TH/A10 = SUBSTR(100, SAMPLE.SAMPLE.NUMERAL, 1, 6, 5, 'A10'); COMPUTE 6TH/A10 = SUBSTR(100, SAMPLE.SAMPLE.NUMERAL, 1, 7, 6, 'A10'); COMPUTE NUM1/D10=ABS(EDIT(1ST)); COMPUTE NUM2/D10=ABS(EDIT(2ND)); COMPUTE NUM3/D10=ABS(EDIT(3RD)); COMPUTE NUM4/D10=ABS(EDIT(4TH)); COMPUTE NUM5/D10=ABS(EDIT(5TH)); COMPUTE NUM6/D10=ABS(EDIT(6TH)); COMPUTE VALU/D7= IF NUM6 GE NUM5 AND NUM5 NE 0 THEN NUM6 ELSE IF NUM5 GE NUM4 AND NUM4 NE 0 THEN NUM5 ELSE IF NUM4 GE NUM3 AND NUM3 NE 0 THEN NUM4 ELSE IF NUM3 GE NUM2 AND NUM2 NE 0 THEN NUM3 ELSE IF NUM2 GE NUM1 AND NUM1 NE 0 THEN NUM2 ELSE NUM1; SAMPLE.SAMPLE.OTHER_TEXT BY TOTAL VALU END OUTPUT VALU NUMERAL 1ST 2ND 3RD 4TH 5TH 6TH NUM1 NUM2 NUM3 NUM4 NUM5 NUM6 VALU OTHER_TEXT 1 1.00 1 1. 1.0 1.00 1.00 1.00 1 1 1 1 1 1 1 simple 2 2.00 2 2. 2.0 2.00 2.00 2.00 2 2 2 2 2 2 2 simple 10 10.00 1 10 10. 10.0 10.00 10.00 1 10 10 10 10 10 10 simple double 15 15 oz 1 15 15 15 o 15 oz 15 oz 1 15 15 0 0 0 15 mixed 100 1001000 oz 1 10 100 100 1001 10010 1 10 100 100 0 0 100 mixed range 1,200 1200 liters 1 12 120 1200 1200 1200 l 1 12 120 1,200 1,200 0 1,200 mixed 2This message has been edited. Last edited by: Todd_Wallace, WebFOCUS 8.1.05 WindowsiSeries DB2, All Outputs HTML  

Gold member 
Another way if there is always a space after/between the numbers and dash/lbs could use SUBSTR to keep only the relevant part of the column and sort that. For example DEFINE FILE CAR COL_1/A63=DECODE MODEL('504 4 DOOR' '1 lb' '2000 4 DOOR BERLINA' '101  500 lbs' '2000 GT VELOCE' '11  20 lbs' '2000 SPIDER VELOCE' '2 lbs' 'DORA 2 DOOR' '3 lbs' ELSE ''); COL_1_SORTING/I7=EDIT(SUBSTR(63,COL_1,1,POSIT(COL_1,63,' ',1,'I7'),POSIT(COL_1,63,' ',1,'I7')1,'A63')); END TABLE FILE CAR BY COL_1_SORTING BY COL_1 WHERE COUNTRY EQ 'ITALY' OR 'FRANCE'; END Although there are a few issues with this if you have both a range of 46 and 48 or 36 will only sort based on the first number. Also, it relies on finding the first space in the value to stop at. If the format isn't always the same will have to account of that or possibly try using PATTERN to get the first digit group and remove everything else. Edit: Didn't think about it until after posted and shut everything down but if is in a nice format can use GETTOK instead of SUBSTR since that way could sort on both the first number value and the second by simply checking if the string has a dash in it use the second number group else use the first token. COL_1_SORTING/I7=EDIT(GETTOK(COL_1,63,1,' ',63,'A63'));This message has been edited. Last edited by: Crymsyn, WF: 8.1.05, OS: Windows, Output: HTML, PDF, Excel  

Member 
I want the result set should be 1 lb, 2 lbs, 3 lbs ...10 lbs, 1120 lbs, 2130 lbs .... 91 lbs100 lbs etc.This message has been edited. Last edited by: Hemin, Apps Studio 8.1.0.5 Windows 7  

Virtuoso 
FILEDEF DATA DISK USERDATA.DAT FILEDEF MAS DISK USERDATA.MAS RUN WRITE DATA 1 lb WRITE DATA 2 lbs WRITE DATA 3 lbs WRITE DATA 1011 lbs WRITE DATA 1120 lbs WRITE DATA 110  542 lbs WRITE DATA 1123 lbs WRITE DATA 10 lbs WRITE DATA 11190 lbs WRITE DATA 2130 lbs WRITE DATA 91 lbs WRITE DATA 100 lbs WRITE DATA 11 lb WRITE DATA 3041 lbs WRITE DATA 1009  2543 lbs WRITE DATA 19 lbs WRITE MAS FILENAME=USERDATA, SUFFIX=FIX WRITE MAS FIELD=NUMBER, FORMAT=A40, ACTUAL=A40, $ RUN DEFINE FILE USERDATA NR1/A40 = IF NUMBER CONTAINS '' THEN GETTOK(NUMBER, 40, 1, '', 40, NR1) ELSE GETTOK(NUMBER, 40, 1, ' ', 40, NR1); NR2/A40 = IF NUMBER CONTAINS '' THEN GETTOK(NUMBER, 40, 2, '', 40, NR2) ELSE '0'; SORT1/I8 = EDIT(NR1); SORT2/I8 = EDIT(GETTOK(NR2, 40, 1, ' ', 40, 'A40')); END TABLE FILE USERDATA PRINT NUMBER BY SORT1 NOPRINT BY SORT2 NOPRINT END GamP
 

Powered by Social Strata 
Please Wait. Your request is being processed... 