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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [solved] Selecting Highest Only

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[solved] Selecting Highest Only
 Login/Join
 
Member
posted
Hi guys, I'm having a little trouble here.
I have the following FOCMAST
FILENAME  = SECFLLOG,SUFFIX  = FOC
SEGNAME   = SECFLROT,SEGTYPE = S4

FIELDNAME = REQ_SYSTEM  ,            ,FORMAT = A5          ,INDEX=I   ,$
FIELDNAME = USERID      ,            ,FORMAT = A8          ,INDEX=I   ,$
FIELDNAME = SEC_MAINT_DT,            ,FORMAT = A8          ,INDEX=I   ,$
FIELDNAME = SEC_TIME    ,            ,FORMAT = A8          ,INDEX=I   ,$
FIELDNAME = FIRST_NAM_BF,            ,FORMAT = A15         ,          ,$
FIELDNAME = FIRST_NAM_AF,            ,FORMAT = A15         ,          ,$
FIELDNAME = LAST_NAME_BF,            ,FORMAT = A25         ,          ,$
FIELDNAME = LAST_NAME_AF,            ,FORMAT = A25         ,          ,$
FIELDNAME = REQ_TYPE    ,            ,FORMAT = A1          ,          ,$
FIELDNAME = REQ_STATUS  ,            ,FORMAT = A1          ,          ,$
FIELDNAME = SEC_OPERID  ,            ,FORMAT = A7          ,          ,$
FIELDNAME = VER_OPERID  ,            ,FORMAT = A7          ,          ,$
FIELDNAME = VER_MAINT_DT,            ,FORMAT = A8          ,          ,$
FIELDNAME = VER_TIME    ,            ,FORMAT = A8          ,          ,$
FIELDNAME = VER_STATUS  ,            ,FORMAT = A1          ,          ,$
FIELDNAME = LEVEL_BFOR  ,            ,FORMAT = A1          ,          ,$
FIELDNAME = LEVEL_AFTR  ,            ,FORMAT = A1          ,          ,$
END


The data in it is generated via a FOCUS application, and sometimes we can have multiple actions for the same system/userid on the same date.
The thing is, at some point in my application I need to get only the last entry, for each userid.
I've been trying this:
TABLE FILE SECFLLOG
SUM FIRST_NAM_BF
    FIRST_NAM_AF
    LAST_NAME_BF
    LAST_NAME_AF
    REQ_TYPE    
    REQ_STATUS  
    SEC_OPERID  
    VER_OPERID  
    VER_MAINT_DT
    VER_TIME    
    VER_STATUS  
    LEVEL_BFOR  
    LEVEL_AFTR  
IF VER_STATUS IS 'A'
BY REQ_SYSTEM
BY USERID
BY HIGHEST SEC_MAINT_DT
BY HIGHEST SEC_TIME
END


And this is producing the following result (I'm purposedly suppressing a few columns of the output here just to be more readable):

REQ_SYSTEM  USERID    SEC_MAINT_DT  SEC_TIME  FIRST_NAM_BF 
----------  ------    ------------  --------  ------------ 
BTCCS       CDALOZB   09/09/14      08.51.05  ARADHANA 
                      09/05/14      09.39.26  ARADHANA 
                                    06.46.31  ARADHANA 
            CDALOZ2   09/09/14      08.54.31  RAJ 
                      09/05/14      09.40.37  RAJ 
                                    06.49.49  RAJ 
            CDALOZ3   09/09/14      08.48.20  ANU 
                      09/05/14      09.32.59  PRADEEP 
                                    06.43.44  ABHINAV 
                      09/03/14      15.04.26  ABHINAV 
                      08/29/14      15.04.42  PRADEEP 
            CDALOZ5   08/29/14      15.07.49  KALYANA 
            CDALOZ6   08/29/14      15.10.33  PAUL 
            CDALOZ7   09/03/14      15.06.13  BOBBY 
WHS         CDALOZB   09/09/14      08.53.55  ARADHANA 
                      09/05/14      09.39.56  ARADHANA 
                                    06.49.02  ARADHANA 
            CDALOZ2   09/09/14      08.55.04  RAJ 
                      09/05/14      09.41.14  RAJ 
                                    06.50.24  RAJ 
            CDALOZ3   09/09/14      08.49.13  ANU 
                      09/05/14      09.38.29  PRADEEP 
                                    06.45.38  ANU 
                      09/03/14      15.05.05  ABHINAV 
                      08/29/14      15.05.43  PRADEEP 
            CDALOZ5   08/29/14      15.08.50  KALYANA 
            CDALOZ6   08/29/14      15.11.19  PAUL 
            CDALOZ7   09/03/14      15.06.51  BOBBY


I've been trying a few things I've read on the documentation, but still couldn't find a way to do the output come as I need it.
I Need the output to come out like this, showing just the last occurence for each system/userid:

REQ_SYSTEM  USERID    SEC_MAINT_DT  SEC_TIME  FIRST_NAM_BF 
----------  ------    ------------  --------  ------------ 
BTCCS       CDALOZB   09/09/14      08.51.05  ARADHANA 
            CDALOZ2   09/09/14      08.54.31  RAJ 
            CDALOZ3   09/09/14      08.48.20  ANU 
            CDALOZ5   08/29/14      15.07.49  KALYANA 
            CDALOZ6   08/29/14      15.10.33  PAUL 
            CDALOZ7   09/03/14      15.06.13  BOBBY 
WHS         CDALOZB   09/09/14      08.53.55  ARADHANA 
            CDALOZ2   09/09/14      08.55.04  RAJ 
            CDALOZ3   09/09/14      08.49.13  ANU 
            CDALOZ5   08/29/14      15.08.50  KALYANA 
            CDALOZ6   08/29/14      15.11.19  PAUL 
            CDALOZ7   09/03/14      15.06.51  BOBBY

Is there a way to do such filtering?
Thanks in advance

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


FOCUS 7.6
Mainframe, Outputs available
 
Posts: 15 | Location: Brasil | Registered: May 21, 2014Report This Post
Guru
posted Hide Post
quote:
BY HIGHEST SEC_TIME

Try changing your code from

BY HIGHEST SEC_TIME
to


BY HIGHEST 1 SEC_TIME


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Member
posted Hide Post
That worked smoothly, thanks a lot.


FOCUS 7.6
Mainframe, Outputs available
 
Posts: 15 | Location: Brasil | Registered: May 21, 2014Report This Post
Guru
posted Hide Post
please tag as solved


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report 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] Selecting Highest Only

Copyright © 1996-2020 Information Builders