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] Showing latest record in IBI
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Showing latest record in IBI
 Login/Join
 
Member
posted
Hi there,

Could anyone please tell me how to get webfocus to only show the latest record for a particular table?

Sample Data:
Account,Suffix,ID,Type,Amount1,Amount2,Date1,Date2,Months
000123,090,621,"Loan",'500','700','2020/04/30','2020/07/01',1
000123,090,621,"Loan",'600','700','2020/06/1','2020/07/01',1

I want the report to only show the following (highest Date1):
000123,090,621,"Loan",'600','700','2020/06/1','2020/07/01',1

I tried displaying LST.Date1 but it did not work.

In SQL I could do something like this:

SELECT *
FROM (
SELECT Account,Suffix,ID,Type,Amount1,Amount2,Date1,Date2,Months,
ROW_NUMBER() OVER ( PARTITION BY Account, Suffix, ID, Type ORDER BY Account DESC ) rn
FROM myTABLE
WHERE something
) q
WHERE rn=1

Thanks
Glen

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 15 | Registered: January 24, 2020Reply With QuoteReport This Post
Virtuoso
posted Hide Post
BY HIGHEST 1 Date1 will limit the records to the one that has the record you're looking for.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
BabakNYC you are a genius. Thank you so much!
 
Posts: 15 | Registered: January 24, 2020Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Showing latest record in IBI

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