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 3 cloumns

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] sort 3 cloumns
 Login/Join
 
Silver Member
posted
Hi,

I have a report something like this

name amount1 amount2 amount3

I want to get the top 10 names.

So, I have to sort these three columns and then i can get the top 10 names. I am not sure how to sort 3 columns.

Please suggest me a solution.\

Thank you,
Priya

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


WebFOCUS 7.7
Windows, All Outputs
 
Posts: 38 | Registered: March 07, 2012Report This Post
Virtuoso
posted Hide Post
Top 10 names based on what?
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
You mean the names for the top 10 amounts.
You have 3 amounts, so you want the top 10 amounts from the 3 columns?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
yes ,

i want the top 10 amounts from each column

Venkat - Thank you so much for the code...
I want the amounts also to be sorted highest. If I modify u r code by adding 'BY HIGHEST' to amount1,2,3 I see that only amount1,amout2 are sorted but i want all the columns to be sorted....

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


WebFOCUS 7.7
Windows, All Outputs
 
Posts: 38 | Registered: March 07, 2012Report This Post
Virtuoso
posted Hide Post
You can get the top 10 names based on one of the amount columns, but not all three - unless you want to base your sort/selection on the total of the three amount columns for each name.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Silver Member
posted Hide Post
Like Dan mentioned, you can sort one of the three amount columns but not all three.

If you want top 10 amounts from the column, you can do BY HIGHEST 10 fieldname

Here is the sample code:
SET ASNAMES = ON
TABLE FILE CAR
SUM
COUNTRY
BY DEALER_COST AS 'AMOUNT1'
BY RETAIL_COST AS 'AMOUNT2'
BY SALES AS 'AMOUNT3'
BY CAR AS 'NAME'
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_CAR
END

TABLE FILE HLD_CAR
PRINT
NAME
BY HIGHEST 10 AMOUNT1
BY HIGHEST AMOUNT2
BY HIGHEST AMOUNT3
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

This message has been edited. Last edited by: Venkat-,


product release:8203
o/s: windows 10
expected o/p formats: HTML,EXCEL,PDF
 
Posts: 34 | Location: Kearney, Nebraska | Registered: December 14, 2011Report This Post
Expert
posted Hide Post
Clear requirements and examples always help in giving an answer.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
From what I get from your edited post Haripriya is that when you look at the entire columns for Amount1, Amount2, and Amount3 they are not all sorted.

In Venkat's example all of the Amount fields are sorted. Sure if you look at the entire report the Amount2 and Amount3 fields may not be sorted but they are sorted within the previous Amount field.

If you want to treat all three Amount fields as the same and then sort them you need it all in one field. The best way to do this without adding together all of the Amounts is to copy each line for each Amount field. To do that you need to use the Macgyver technique.

Here is an example that will tell you which Amount field the value came from

SET ASNAMES = ON
TABLE FILE GGSALES
PRINT
COMPUTE NAME/A255=PCD || ' ' | STCD ;
UNITS AS 'AMOUNT1'
COMPUTE NEW_DOLLARS/I7=DOLLARS/10; AS 'AMOUNT2'
BUDUNITS AS 'AMOUNT3'
ON TABLE HOLD AS HOLD_AMTS
END

FILEDEF MCGMAS DISK MACGYVER.MAS
FILEDEF MCGDAT DISK MACGYVER.DAT
-RUN
-WRITE MCGMAS FILE=MACGYVER,SUFFIX=FOC
-WRITE MCGMAS SEGNAME=MAC1,SEGTYPE=S1
-WRITE MCGMAS   FIELD=BLANK  ,     ,A1,INDEX=I,$
-WRITE MCGMAS SEGNAME=MAC2,SEGTYPE=S1,PARENT=MAC1
-WRITE MCGMAS   FIELD=COUNTER,ORDER,I4,$
-RUN

 CREATE FILE MACGYVER
 MODIFY FILE MACGYVER
 COMPUTE CTR/I9=;
 FIXFORM 3(CTR/4 X-4)
 COMPUTE
   BLANK=' ';
   COUNTER=IF COUNTER EQ 0 THEN CTR ELSE COUNTER+1;
 MATCH BLANK
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 MATCH COUNTER
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 DATA
1
 END
-RUN

JOIN
BLANK WITH NAME IN HOLD_AMTS TO
BLANK IN MACGYVER AS B_
END

DEFINE FILE HOLD_AMTS
BLANK/A1 WITH NAME=' ';

AMOUNT_VALUE/I7=IF COUNTER EQ 1 THEN AMOUNT1
ELSE IF COUNTER EQ 2 THEN AMOUNT2
ELSE IF COUNTER EQ 3 THEN AMOUNT3
ELSE 0;

AMOUNT_FROM_FIELD/A10=IF COUNTER EQ 1 THEN 'AMOUNT1'
ELSE IF COUNTER EQ 2 THEN 'AMOUNT2'
ELSE IF COUNTER EQ 3 THEN 'AMOUNT3'
ELSE '';
END

TABLE FILE HOLD_AMTS
PRINT 
NAME
AMOUNT_FROM_FIELD
AMOUNT1
AMOUNT2
AMOUNT3
BY HIGHEST 10 AMOUNT_VALUE
END


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
Virtuoso
posted Hide Post
Perhaps, as Crymsyn suggests, you want the top names based on the highest amount in any of the amount columns? If so, here's another way to do that:

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY
-*
TABLE FILE CAR
 SUM
  DEALER_COST/D8  AS 'AMOUNT1'
  RETAIL_COST/D8  AS 'AMOUNT2'
  SALES/D8        AS 'AMOUNT3'
 BY CAR           AS 'NAME'
 ON TABLE HOLD AS DATAFILE
END
-*
DEFINE FILE DATAFILE
 AMOUNT_TOP/D8 = MAX(AMOUNT1,AMOUNT2,AMOUNT3);
END
-*
TABLE FILE DATAFILE
 PRINT
  NAME
  AMOUNT1
  AMOUNT2
  AMOUNT3
  AMOUNT_TOP
 BY HIGHEST 10 AMOUNT_TOP NOPRINT
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
Dan,

You had the same idea as I had. This can be done in one pass:
  
-* File Priya02.fex
TABLE FILE CAR
SUM SALES RCOST DCOST
COMPUTE MAXVAL/D6=MAX(SALES, RCOST, DCOST);
BY TOTAL HIGHEST 5 MAXVAL
BY CAR
END

and add NOPRINT wherever desired.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
Pfiou ...
The question seemed rather Low Level, but you all, great Focusians, made it a rather great exchange.
To multiply a dimension of a file (Crymsin approach) a simple Idea is to
AND  COMPUTE                      
     DIMGEO/I02 = 3 ;             
AND  COMPUTE                      
     DETGEO_GLOB   /A03 = '123' ; 

And overhead those Fields by describing them in the Master
 
FIELDNAME   =DIMGEO,        ,I02  , I04,$                            
$                                                                    
$  PSEUDO FILS DE NIVEAU MAT (POSSIBLE SUR FIX UNIQUEMENT)           
$                                                                    
SEGNAME=PSEUMAT,PARENT=SEDG111A,OCCURS=DIMGEO,$                      
FIELD=DETGEO      ,       ,A01       ,A01  ,$  NIVEAU AGREG DEMANDE  
FIELD=INDGEO      ,ORDER  ,I01       ,I04  ,$  RESTITUE L'INDICE MAT  

So, you have (at the PSEUMAT level ) 3 times the Data of the Father Segment. (Define must use WITH DETGEO to bring Father Data at PseuMat level)
This pre-historical technique led to Multi-Dimensional approach (Defining the hierarchy of a dimenson, Within etc ...)
From the past, with love
Focusely


Focus Mainframe 7.6.11
Dev Studio 7.6.11 and !!!
PC Focus, Focus for OS/2, FFW Six, MSO
 
Posts: 134 | Registered: November 06, 2007Report This Post
Silver Member
posted Hide Post
Hi All,

I got the correct requirements now...they wanted to sort on the total comun(amount1+amount2+amount3) which is very meaningful....so it soves the problem....Thank u all for the responses...Smiler

Priya


WebFOCUS 7.7
Windows, All Outputs
 
Posts: 38 | Registered: March 07, 2012Report 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 3 cloumns

Copyright © 1996-2020 Information Builders