Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SORT BUT RETAIN HIGHEST VALUE ON NON-SORTED FIELDS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: SORT BUT RETAIN HIGHEST VALUE ON NON-SORTED FIELDS
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 User


Joined: 29 Apr 2008
Posts: 247
Location: Maryland

PostPosted: Fri Dec 15, 2017 3:42 am    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sum the fields in particular format f... sharana64 DFSORT/ICETOOL 9 Fri Jan 05, 2018 1:11 am
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us