Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Sorting alphanumeric values

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Sorting alphanumeric values
 Login/Join
 
Member
posted
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,


Apps Studio 8.1.0.5
Windows 7
 
Posts: 29 | Registered: March 02, 2016Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
column1 includes alphanumeric. eg: 1 lb, 2 lbs, 3 lbs etc.


Apps Studio 8.1.0.5
Windows 7
 
Posts: 29 | Registered: March 02, 2016Report This Post
Virtuoso
posted Hide Post
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 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
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
 
Posts: 29 | Registered: March 02, 2016Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
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,


WebFOCUS 8.1.05
Windows-iSeries DB2, All Outputs
HTML
 
Posts: 29 | Location: Cincinnati, OH | Registered: August 23, 2011Report This Post
Gold member
posted Hide Post
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,


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
Member
posted Hide Post
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,


Apps Studio 8.1.0.5
Windows 7
 
Posts: 29 | Registered: March 02, 2016Report This Post
Virtuoso
posted Hide Post
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


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Sorting alphanumeric values

Copyright © 1996-2020 Information Builders