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] sort by values in across column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] sort by values in across column
 Login/Join
 
Platinum Member
posted
I thought I saw a post relating to this but now I can't find it, so sorry for the repeat...

I'm trying to sort by a specific column in the across values. For example, I'm trying to sort the following by the SEDAN column:
TABLE FILE CAR
SUM CAR
BY COUNTRY AS ''
ACROSS BODYTYPE AS ''
ON TABLE NOTOTAL
END


I've tried using a DEFINE to sort by (eg, IF BODYTYPE EQ 'SEDAN' THEN ...) but the way I tried it, it inserted a few extra rows.

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


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Expert
posted Hide Post
Not quite sure what you want. Did you mean the ACROSS fieldname COLUMNS value1 value2 ... valuen?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
Nope. Run the code above and you'll see that the rows are sorted by COUNTRY. I want the exact same output but instead of sorted by COUNTRY it should be sorted by the values in the SEDAN "column" which is actually a crosstab value.


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Expert
posted Hide Post
I'm not sure really what you want either... But, my best interpretation may be something like this produces:
TABLE FILE CAR
SUM DCOST
ACROSS CAR
WHERE BODYTYPE EQ 'SEDAN'
ON TABLE NOTOTAL
ON TABLE SET PAGE OFF
END
Which SUMs the DCOST ACROSS Car only for SEDANs.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Platinum Member
posted Hide Post
Run the code in my first post...you'll see the rows are sorted by country. I'm trying to sort the rows by the values in the SEDAN column, intead of COUNTRY. So instead of:

           CONVERTIBLE  COUPE    HARDTOP  ROADSTER    SEDAN
ENGLAND    JAGUAR                TRIUMPH              JENSEN
FRANCE                                                PEUGEOT
ITALY                   MASERATI          ALFA ROMEO  ALFA ROMEO
JAPAN                                                 TOYOTA
W GERMANY                                             BMW


...I want this...

           CONVERTIBLE  COUPE    HARDTOP  ROADSTER    SEDAN
ITALY                   MASERATI          ALFA ROMEO  ALFA ROMEO
W GERMANY                                             BMW
ENGLAND    JAGUAR                TRIUMPH              JENSEN
FRANCE                                                PEUGEOT
JAPAN                                                 TOYOTA


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Virtuoso
posted Hide Post
Here is one approach. The first TABLE FILE writes out the column names (BODYTYPE) for use in the final output. These values are read into variables via a -REPEAT loop. The second TABLE FILE extracts the necessary data and places it in a HOLD file. The final TABLE FILE sorts the data extract as desired and generates the report using the original column names.

TABLE FILE CAR
 SUM CAR NOPRINT
 BY BODYTYPE
 ON TABLE SAVE AS 'NAMES'
END
-*
-RUN
-SET &NAMECNT = &LINES + 1 ;
-*
-REPEAT ENDREPEAT1 FOR &I FROM 2 TO &NAMECNT
-READ NAMES, &NAME.&I
-ENDREPEAT1
-*
TABLE FILE CAR
 SUM CAR
 BY COUNTRY
 ACROSS BODYTYPE
 ON TABLE HOLD
END
-*
TABLE FILE HOLD
 PRINT E01 AS ''
-REPEAT ENDREPEAT2 FOR &I FROM 2 TO &NAMECNT
 E0&I AS '&NAME.&I'
-ENDREPEAT2
-SET &BYI = &I - 1 ;
 BY E0&BYI NOPRINT
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
... or if you do not want to use DM to loop around then you could extract the order and then join back using your hold as the parent and driver table -

APP PREPENDPATH IBISAMP
TABLE FILE CAR
  SUM CAR      AS MYSORT
   BY COUNTRY
WHERE BODYTYPE EQ 'SEDAN'
ON TABLE HOLD AS MYHOLD
END
JOIN CLEAR *
JOIN COUNTRY IN MYHOLD TO COUNTRY IN CAR AS J1
TABLE FILE MYHOLD
  SUM CAR       AS ''
   BY MYSORT    NOPRINT
   BY COUNTRY   AS ''
ACROSS BODYTYPE AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Is it this what you want ?

It is probably not the simplest way but it seems to work.

I don't know whether the content of 'car' in my release (7.1.6) is the same as in yours.
I noticed by trying this that max. and lst. don't work together with within and alpha-fields. Is that a bug or a feature ?.
So I had to find a workaround like "last country"

DEFINE FILE CAR
SORTX/A16 = IF COUNTRY NE LAST COUNTRY THEN ' ' ELSE SORTX;
SORTX = IF BODYTYPE EQ 'SEDAN' THEN CAR ELSE SORTX;
END


TABLE FILE CAR
SUM CAR

BY COUNTRY
BY HIGHEST SORTX
BY BODYTYPE

ON TABLE HOLD
END

DEFINE FILE HOLD
SORTY/A16 = IF COUNTRY NE LAST COUNTRY THEN SORTX ELSE SORTY;
END

TABLE FILE HOLD
SUM FST.CAR

BY SORTY NOPRINT
BY COUNTRY

ACROSS BODYTYPE

END


7.1.6, Windows
all output
 
Posts: 2 | Registered: December 15, 2009Report This Post
Platinum Member
posted Hide Post
Tony
Nice. Also, your way would allow you to parameterize the value to sort by.

The only problem is if you use a different BODYTYPE, say 'ROADSTER'. Your first hold gives you a single record (the Alpha Romeo from Italy) which restricts the final result set. So I switched the from/to tables in the join so I could do a LEFT_OUTER and ran into indexing errors.

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


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Virtuoso
posted Hide Post
Another alternative. Note that the AS option for CAR is a space (' ') and not a null ('').

DEFINE FILE CAR
 SORT_SEDAN/A16 = IF BODYTYPE EQ 'SEDAN' THEN CAR ELSE ' ';
END
-*
TABLE FILE CAR
 SUM CAR AS ' '
 BY TOTAL MAX.SORT_SEDAN NOPRINT
 BY COUNTRY AS ''
 ACROSS BODYTYPE AS ''
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
Dan:
Awesome. That's EXACTLY what I was looking for. I had DEFINE'd a sort val, as you have, but totally missed the BY TOTAL MAX...etc. Also, the spaces are genius. This is awesome. And, like Tony's, this would allow for a parameterized sort as well.


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Virtuoso
posted Hide Post
Don't know if it matters or not, but the table you're showing is incomplete because you have summed up alpha values. You're missing the W GERMANY AUDI, the ENGLAND JAGUAR SEDAN, the ITALY ALPHA ROMEO COUPE, and the JAPAN DATSUN SEDAN. I understand that this is probably just an example of what you're trying to do, and may work for your actual need, but it just caught my eye that the code that you were accepting as functional was missing data. Especially since you're wanting to sort on the values in SEDAN, I would think that you would actually want to see all of the valid values.

You can see this by using
SET NODATA=''
TABLE FILE CAR
SUM CAR
BY CAR NOPRINT
BY COUNTRY
ACROSS BODYTYPE
END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Darin
Yes, it was just an example, but that's good to know as well. I had noticed that I would lose records periodically, depending on the grouping, etc.


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 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] sort by values in across column

Copyright © 1996-2020 Information Builders