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,11-20 lbs,101-500 lbs etc. Can I anyone suggest way to do.

This message has been edited. Last edited by: FP Mod Chuck,

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 101-500 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,

column1 includes alphanumeric. eg: 1 lb, 2 lbs, 3 lbs etc.

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

What about these kind of values 101 - 500 lbs,11 - 20 lbs etc. We cant give DECODE for many.

Using DECODE, you will need to list EVERY POSSIBLE values.
It could be a pain if you have a large list or "unknown/not pre-defined" 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

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
100-1000 oz mixed range
2.00 simple
1200 liters mixed 2

CODE
TABLE FILE SAMPLE
PRINT
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 100-1000 oz 1 10 100 100- 100-1 100-10 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 2

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

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 4-6 and 4-8 or 3-6 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,

I want the result set should be 1 lb, 2 lbs, 3 lbs ...10 lbs, 11-20 lbs, 21-30 lbs .... 91 lbs-100 lbs etc.

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

quote:

1 lb, 2 lbs, 3 lbs ...10 lbs, 11-20 lbs, 21-30 lbs .... 91 lbs-100 lbs

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 10-11 lbs
-WRITE DATA 11-20 lbs
-WRITE DATA 110 - 542 lbs
-WRITE DATA 11-23 lbs
-WRITE DATA 10 lbs
-WRITE DATA 11-190 lbs
-WRITE DATA 21-30 lbs
-WRITE DATA 91 lbs
-WRITE DATA 100 lbs
-WRITE DATA 11 lb
-WRITE DATA 30-41 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