IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SORT BUT RETAIN HIGHEST VALUE ON NON-SORTED FIELDS


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
leondan22

New User


Joined: 13 Dec 2017
Posts: 2
Location: USA

PostPosted: Thu Dec 14, 2017 8:13 pm
Reply with quote

We receive an employee file from an outside entity in fixed block format. Record length = 36. There can be multiple records for an employee if they work in more than one position. The key fields to group on are AGENCY_CD and EMPLOYEE_ID. Our objective is to combine multiple records for an employee into 1 record, given the following criteria:
    Select non-TEMP (permanent) position first.
    If multiple records for an employee are non-TEMP or all are TEMP, select most recent appointment date.).
    If all records have the same appointment date, select the highest position ID (which is unique).

It would be easy enough to group on AGENCY_CD and EMPLOYEE_ID, sort the records per the criteria above and then SUM to remove the duplicate records, but there's a catch. Each record has 4 privacy indicators with a value of 'Y' or 'N'. We want the strictest privacy indicator value ('Y') for each indicator for that employee in our output record. Example (focusing on the record with AGENCY_CD = 110000 AND EMPLOYEE_ID = 000000002):

INPUT:
Code:

AGENCY_CD   EMPLOYEE_ID   POSITION_ID   TEMP_IND   APPT_DT      PRIVACY_1   PRIVACY_2   PRIVACY_3   PRIVACY_4
110000      000000001     111111        N          2000-08-01   N           N           N           N
110000      000000002     222222        N          2014-01-22   Y           N           Y           N
110000      000000002     333333        Y          2017-11-03   N           Y           N           N
210000      000000001     121212        N          2015-07-15   N           N           N           N


DESIRED OUTPUT:
Code:

AGENCY_CD   EMPLOYEE_ID   POSITION_ID   TEMP_IND   APPT_DT      PRIVACY_1   PRIVACY_2   PRIVACY_3   PRIVACY_4
110000      000000001     111111        N          2000-08-01   N           N           N           N
110000      000000002     222222        N          2014-01-22   Y           Y           Y           N
210000      000000001     121212        N          2015-07-15   N           N           N           N


Any help would be greatly appreciated!
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 931
Location: Maryland

PostPosted: Fri Dec 15, 2017 3:42 am
Reply with quote

I'm lazy to test it, but try to use MAX operation in SORT, for your privacy columns.
'Y' value is greater than 'N' when considered in format BI.
Back to top
View user's profile Send private message
leondan22

New User


Joined: 13 Dec 2017
Posts: 2
Location: USA

PostPosted: Fri Dec 15, 2017 5:06 am
Reply with quote

sergeyken wrote:
I'm lazy to test it, but try to use MAX operation in SORT, for your privacy columns.
'Y' value is greater than 'N' when considered in format BI.


For the employee in my example with multiple position IDs, I would select POSITION_ID 222222 because it is the non-temporary position. PRIVACY_1 has a value of 'Y', so if I sort by max value for PRIVACY_1, all is good. However, the PRIVACY_2 indicator for that record has a value of 'N' whereas PRIVACY_2 indicator for that same employee in POSITION_ID 333333 has a value of 'Y'. Since I need the strictest value for each of the privacy indicators, I would need to replace the value of the PRIVACY_2 indicator for POSITION_ID 222222 with the PRIVACY_2 value found in POSITION_ID 333333. I hope that makes sense! icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Very Large Sort failed - Seeking reco... DFSORT/ICETOOL 7
No new posts Date conversion using SORT SYNCSORT 4
No new posts Which SORT utility can improve the Pe... DFSORT/ICETOOL 16
No new posts Using sort on VB records based on off... DFSORT/ICETOOL 6
No new posts COBOL VS SORT Utility for file format... COBOL Programming 6
Search our Forums:

Back to Top