Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]function to give the Actual LAST value
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]function to give the Actual LAST value
 Login/Join
 
Gold member
posted
Hello All,

I was wondering if there is a function in WF that gives us the actual LAST value for a given particular field.
We have a vendored application that gives the actual last value for a text/numeric field.

For e.g: If there is a field named Amount whose values are 10,20, 30,40,50.. the function should retrieve the last value for it as 50 and display in the last row.

I have looked at most of the functions and couldnt find anything.
Hoping someone can guide me here.

Thanks for reading,

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


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Guru
posted Hide Post
if you want the last entry it is LST.fieldname, if you the largest value it is MAX.fieldname


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
Hello RSquared

I have used the LST function but it gives the last value in the first row.
I want the last value to be displayed in the last row (position the value in the last row just before the grouping chnages).
Also the current field is not grouped.

I have attached a link of the image. The users want the circled amount in the yellow location.




Thanks,


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
or "LAST"
 
Posts: 3066 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Sumant,

I am sorry but I am not able to see the picture you sent. Can you please copy and paste the report into a posting?

thank you


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
 
-SET &ECHO=ALL;
SET EMPTYREPORT = ON
-*===============================================================================================================================================================================================
DEFINE FILE X_FI_PORTFOLIO_CASH_126
receipt_amt/P20.2B=IF X_FI_PORTFOLIO_CASH_126.ANSWERSET1.AMOUNT GT 0 THEN X_FI_PORTFOLIO_CASH_126.ANSWERSET1.AMOUNT ELSE 0;
payment_amt/P20.2B=IF X_FI_PORTFOLIO_CASH_126.ANSWERSET1.AMOUNT LT 0 THEN X_FI_PORTFOLIO_CASH_126.ANSWERSET1.AMOUNT ELSE 0;
END
-*===============================================================================================================================================================================================
TABLE FILE X_FI_PORTFOLIO_CASH_126
PRINT 
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.payment_dt_MDYY AS 'Cash Flow,Date'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.LEG_ID AS 'Instrument,'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.CP_CLIENT_ID AS 'Cpty'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.STANDARD_ID AS 'Security,Identifier'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.TARGET_TRANSACTION_ID AS 'Transaction,#'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.type AS 'Type'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.STATE_ID AS 'Transaction,Sate'
     COMPUTE opening_bal/D20.2S = IF X_FI_PORTFOLIO_CASH_126.ANSWERSET1.OPENING_BALANCE EQ LAST X_FI_PORTFOLIO_CASH_126.ANSWERSET1.OPENING_BALANCE THEN 0 ELSE X_FI_PORTFOLIO_CASH_126.ANSWERSET1.OPENING_BALANCE; AS 'Opening,Balance'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.receipt_amt/P20.2B AS 'Receipt,Amount'
     X_FI_PORTFOLIO_CASH_126.ANSWERSET1.payment_amt/P20.2B AS 'Payment,Amount'
     COMPUTE closing_bal/D20.2S = IF X_FI_PORTFOLIO_CASH_126.ANSWERSET1.CLOSING_BALANCE EQ LAST X_FI_PORTFOLIO_CASH_126.ANSWERSET1.CLOSING_BALANCE THEN 0 ELSE X_FI_PORTFOLIO_CASH_126.ANSWERSET1.CLOSING_BALANCE; AS 'Closing,Balance'
BY  X_FI_PORTFOLIO_CASH_126.ANSWERSET1.PORTFOLIO_ID AS 'Portfolio'
BY  X_FI_PORTFOLIO_CASH_126.ANSWERSET1.report_dt AS 'Report,Date'
BY  HIGHEST X_FI_PORTFOLIO_CASH_126.ANSWERSET1.receipt_amt NOPRINT 
BY  LOWEST X_FI_PORTFOLIO_CASH_126.ANSWERSET1.payment_amt NOPRINT
WHERE ( X_FI_PORTFOLIO_CASH_126.INPUT.I_INSTANCE EQ '&ENV_VAR' )
AND ( X_FI_PORTFOLIO_CASH_126.INPUT.I_TAG EQ '&TAG' )
AND ( X_FI_PORTFOLIO_CASH_126.INPUT.I_TAG2 EQ '&TAG2' )
AND ( X_FI_PORTFOLIO_CASH_126.INPUT.I_PORTFOLIO_ID EQ '&PORTFOLIO' )
AND ( X_FI_PORTFOLIO_CASH_126.INPUT.I_CURRENCY_ID EQ '&CCY' )
AND ( X_FI_PORTFOLIO_CASH_126.INPUT.I_FROM_DATE EQ DT(&FROM_VALUE_DATE.PLEASE ENTER THE FROM DATE.) )
AND ( X_FI_PORTFOLIO_CASH_126.INPUT.I_TO_DATE EQ DT(&TO_VALUE_DATE.PLEASE ENTER THE TO DATE.) );
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE HOLD AS &TEMP_HOLD_NAME FORMAT &WFFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = gtms_sty,
$
     PAGESIZE='A3',
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
ENDSTYLE
END
 


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Gold member
posted Hide Post
Hello Rsquared,

I have posted the code above.
There are 2 computes in the code (opening and closing balance). The users want the opening balance to be seen in the first row of the grouping and the clsing balance to be seen in the last row of the grouping.
I basically have to display the LST.value of the clsing balnce in the last row.


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
TABLE FILE IBISAMP/CAR
SUM DEALER_COST
BY COUNTRY BY CAR BY MODEL
RUN
SUM FST.DEALER_COST LST.DEALER_COST
BY COUNTRY BY CAR
END




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Hello Susannah,

Your code was not what I was expecting.
It was my mistake that I could not explain to everyone correctly.
I have put in a sample CAR file code.
My users want to see the value of RC in the spot corresponding to ENGLAND/TRIUMPH instead of ENGLAND/JAGUAR.

DEFINE FILE CAR
DC_123/D20.2 = 12345.6;
RC_123/D20.2 = 67890.1;
END

TABLE FILE CAR
PRINT 
     CAR.ORIGIN.COUNTRY
     CAR.COMP.CAR
     COMPUTE DC/D20.2S = IF DC_123 EQ LAST DC_123 THEN 0 ELSE DC_123;
     COMPUTE RC/D20.2S = IF RC_123 EQ LAST RC_123 THEN 0 ELSE RC_123;
BY  CAR.ORIGIN.COUNTRY
WHERE CAR.ORIGIN.COUNTRY EQ 'ENGLAND';
ON TABLE NOTOTAL
ON TABLE SET STYLE *
$
ENDSTYLE
END


Thanks,


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
Master
posted Hide Post
You could try sorting BY HIGHEST 1 AMOUNT if the last value is always the greatest. Or you could try the LIST verb and then retrieve the last value in the list. Also look at RANKED.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Master
posted Hide Post
quote:
My users want to see the value of RC in the spot corresponding to ENGLAND/TRIUMPH instead of ENGLAND/JAGUAR.

I believe a possible solution to this requirement was given in the following recent post:
http://forums.informationbuild...907056236#8907056236
SET WIDTH = 200, PANEL=200
DEFINE FILE CAR
 DC_123/D20.2 = 12345.6;
 RC_123/D20.2 = 67890.1;
END

TABLE FILE CAR
SUM CNT.CAR NOPRINT 
BY  COUNTRY NOPRINT
-*
PRINT 
     COUNTRY
     CAR
	 COMPUTE ROWNUM    = IF COUNTRY EQ LAST COUNTRY THEN ROWNUM + 1 ELSE 1; NOPRINT
     COMPUTE DC/D12.2S = IF C1      EQ ROWNUM       THEN DC_123     ELSE 0;
     COMPUTE RC/D12.2S = IF C1      EQ ROWNUM       THEN RC_123     ELSE 0;
BY   COUNTRY NOPRINT
BY   CAR     NOPRINT
-*
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT STANDARD
END  
1 PAGE     1
  
  
  COUNTRY     CAR                           DC              RC                  
  -------     ---                           --              --                  
  ENGLAND     JAGUAR                                          
  ENGLAND     JENSEN                                          
  ENGLAND     TRIUMPH                12,345.60       67,890.10
  

This message has been edited. Last edited by: David Briars,




Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2012/Tomcat Standalone Workstation: Windows 7/IE 11 Database: Oracle 12c/Netezza Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer, & ReportCaster

 
Posts: 789 | Registered: April 23, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
David I think this is more what he wanted (DC is still at the top):
 
TABLE FILE CAR
PRINT 
     CAR.COMP.CAR
     COMPUTE CAR_COUNTER/I9 = IF CAR.ORIGIN.COUNTRY EQ LAST CAR.ORIGIN.COUNTRY THEN CAR_COUNTER + 1 ELSE 0;
BY  CAR.ORIGIN.COUNTRY
ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE CAR
SUM 
     CNT.CAR.COMP.CAR
BY  CAR.ORIGIN.COUNTRY
ON TABLE HOLD AS HOLD2
END
-RUN

JOIN
 LEFT_OUTER HOLD1.HOLD1.COUNTRY IN HOLD1 TO UNIQUE HOLD2.HOLD2.COUNTRY IN HOLD2
 TAG J0 AS J0
 END

TABLE FILE HOLD1
PRINT 
     HOLD1.HOLD1.COUNTRY
     HOLD1.HOLD1.CAR
     COMPUTE DC/D20.2S = IF HOLD1.HOLD1.CAR_COUNTER EQ 0 THEN 12345.6 ELSE 0;
     COMPUTE RC/D20.2S = IF HOLD1.HOLD1.CAR_COUNTER EQ J0.HOLD2.CAR-1 THEN 67890.1 ELSE 0;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Reply With QuoteReport This Post
Master
posted Hide Post
quote:
DC is still at the top

IF DC is required to be at the 'top' and RC is required to be at the 'bottom':
SET WIDTH = 200, PANEL=200
DEFINE FILE CAR
 DC_123/D20.2 = 12345.6;
 RC_123/D20.2 = 67890.1;
END

TABLE FILE CAR
SUM CNT.CAR NOPRINT
BY  COUNTRY NOPRINT
-*
PRINT
     COUNTRY
     CAR
     COMPUTE ROWNUM    = IF COUNTRY EQ LAST COUNTRY THEN ROWNUM + 1 ELSE 1; NOPRINT
     COMPUTE DC/D12.2S = IF ROWNUM  EQ 1            THEN DC_123     ELSE 0;
     COMPUTE RC/D12.2S = IF C1      EQ ROWNUM       THEN RC_123     ELSE 0;
BY   COUNTRY NOPRINT
BY   CAR     NOPRINT
-*
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT STANDARD
END  

1 PAGE     1
  
  
  COUNTRY     CAR                           DC              RC                  
  -------     ---                           --              --                  
  ENGLAND     JAGUAR                 12,345.60                
  ENGLAND     JENSEN                                          
  ENGLAND     TRIUMPH                                67,890.10
  

This message has been edited. Last edited by: David Briars,
 
Posts: 789 | Registered: April 23, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks a lot David, J and George. Your suggestions were really helful and helped me out. Smiler


WebFOCUS 7.7.02Windows
Output Formats: Excel, HTML, PDF
 
Posts: 82 | Registered: February 23, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]function to give the Actual LAST value

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.