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,
Apps Studio 8.1.0.5 Windows 7
August 23, 2017, 08:33 AM
BabakNYC
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,
WebFOCUS 8206, Unix, Windows
August 23, 2017, 08:38 AM
Hemin
column1 includes alphanumeric. eg: 1 lb, 2 lbs, 3 lbs etc.
Apps Studio 8.1.0.5 Windows 7
August 23, 2017, 08:40 AM
BabakNYC
Then you have to use DECODE COLUMN1(1 lb, 1, 2 lbs, 2....); and add BY SORT_ORD NOPRINT
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
August 23, 2017, 11:14 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
August 23, 2017, 01:53 PM
Todd_Wallace
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
WebFOCUS 8.1.05 Windows-iSeries DB2, All Outputs HTML
August 23, 2017, 07:39 PM
Crymsyn
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.
This message has been edited. Last edited by: Crymsyn,
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
August 24, 2017, 02:16 AM
Hemin
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,
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