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] Selecting Highest Only
Go
New
Search
Notify
Tools
Reply
  
[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, 2014Reply With QuoteReport 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, 2008Reply With QuoteReport 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, 2014Reply With QuoteReport 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, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

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