Focal Point
[solved] Selecting Highest Only

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6887085276

September 09, 2014, 09:24 AM
andrekilik
[solved] Selecting Highest Only
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
September 09, 2014, 09:32 AM
RSquared
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
September 09, 2014, 09:47 AM
andrekilik
That worked smoothly, thanks a lot.


FOCUS 7.6
Mainframe, Outputs available
September 09, 2014, 11:04 AM
RSquared
please tag as solved


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit