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

How to take the maximum value of two column


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

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Tue Aug 06, 2013 9:49 am
Reply with quote

Hi ,

I have an requirement as below

Code:

A   B         C    D(B+C)
1   5.10     5.10   10.20
2   10.52   11.00   21.52
2   15.00    9.00   24.00

A   B          C    D(B+C)
1   5.10     5.10   10.20
2   15.00   11.00   26.00

How to acheive the same using DFSORT/ICETOOL
    Back to top
    View user's profile Send private message
    Bill Woodger

    Moderator Emeritus


    Joined: 09 Mar 2011
    Posts: 7309
    Location: Inside the Matrix

    PostPosted: Tue Aug 06, 2013 12:28 pm
    Reply with quote

    Does your sample data fully represent your actual data? The main point being, can there be only two records with the same key?
    Back to top
    View user's profile Send private message
    srajanbose

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 12:31 pm
    Reply with quote

    No,There can n number of records with same key values. From that i need the max of B and C and compute the same to get value of D
    Back to top
    View user's profile Send private message
    Bill Woodger

    Moderator Emeritus


    Joined: 09 Mar 2011
    Posts: 7309
    Location: Inside the Matrix

    PostPosted: Tue Aug 06, 2013 12:36 pm
    Reply with quote

    Well, post some data that reflects that then please.
    Back to top
    View user's profile Send private message
    Pandora-Box

    Global Moderator


    Joined: 07 Sep 2006
    Posts: 1592
    Location: Andromeda Galaxy

    PostPosted: Tue Aug 06, 2013 12:48 pm
    Reply with quote

    And also post the expected output in code tags your post doesnt say anything at all
    Back to top
    View user's profile Send private message
    srajanbose

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 12:48 pm
    Reply with quote

    Input
    Code:
    ----+----1----+----2----+----3----+--
    ***************************** Top of
    20130804 112101527R 10.12 14.02 24.14
    20130804 112101527R 15.00 14.00 29.00
    20130804 112740002R 07.00 09.00 16.00
    20130804 112740002R 08.00 14.00 24.00
    20130804 112740002R 08.00 11.00 18.00
    20130804 112748371R 01.01 02.02 03.03
    20130804 112749532R 00.97 02.03 03.00


    Expected Output
    Code:
    ----+----1----+----2----+----3----+--
    ***************************** Top of
    20130804 112101527R 15.00 14.02 29.02
    20130804 112740002R 08.00 14.00 22.00
    20130804 112748371R 01.01 02.03 03.04
    Back to top
    View user's profile Send private message
    Pandora-Box

    Global Moderator


    Joined: 07 Sep 2006
    Posts: 1592
    Location: Andromeda Galaxy

    PostPosted: Tue Aug 06, 2013 12:59 pm
    Reply with quote

    Why is this record not in output?

    Code:
    20130804 112749532R 00.97 02.03 03.00
    Back to top
    View user's profile Send private message
    srajanbose

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 1:03 pm
    Reply with quote

    Hi ,

    Sorry its my mistake. Please find below the expected output

    Code:
    ----+----1----+----2----+----3----+--
    ***************************** Top of
    20130804 112101527R 15.00 14.02 29.02
    20130804 112740002R 08.00 14.00 22.00
    20130804 112748371R 01.01 02.02 03.03
    20130804 112749532R 00.97 02.03 03.00
    Back to top
    View user's profile Send private message
    Pandora-Box

    Global Moderator


    Joined: 07 Sep 2006
    Posts: 1592
    Location: Andromeda Galaxy

    PostPosted: Tue Aug 06, 2013 1:58 pm
    Reply with quote

    1.You could achieve the solution by sorting the file 21,5 descending removing duplicates for Key 1,20 -> File 1
    2.Then sorting the file 27,5 descending removing duplicates for Key 1,20 -> File 2
    3.Join File 1 & File 2 and do the sum after reformat
    Back to top
    View user's profile Send private message
    srajanbose

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 2:03 pm
    Reply with quote

    Is there any way to acheive the same in single step?
    Back to top
    View user's profile Send private message
    Pandora-Box

    Global Moderator


    Joined: 07 Sep 2006
    Posts: 1592
    Location: Andromeda Galaxy

    PostPosted: Tue Aug 06, 2013 2:15 pm
    Reply with quote

    Yes through ICETOOL
    Back to top
    View user's profile Send private message
    Bill Woodger

    Moderator Emeritus


    Joined: 09 Mar 2011
    Posts: 7309
    Location: Inside the Matrix

    PostPosted: Tue Aug 06, 2013 3:27 pm
    Reply with quote

    Yes, it is possible, in one step, with SORT. But no-one is going to just drop everything to do your work.

    I'd start with this.
    Back to top
    View user's profile Send private message
    Skolusu

    Senior Member


    Joined: 07 Dec 2007
    Posts: 2205
    Location: San Jose

    PostPosted: Wed Aug 07, 2013 9:48 pm
    Reply with quote

    Ideally I would Use a 2 pass approach instead of sort.

    1. Get the max of both fields using Sections and Trailer3 with Max function.
    2. Perform the Arithmetic of max values
    Back to top
    View user's profile Send private message
    srajanbose

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 13, 2013 3:43 pm
    Reply with quote

    Hi,

    I got the solution using ICETOOL in 3 steps.

    Thanks all for your response.
    Back to top
    View user's profile Send private message
    Bill Woodger

    Moderator Emeritus


    Joined: 09 Mar 2011
    Posts: 7309
    Location: Inside the Matrix

    PostPosted: Tue Aug 13, 2013 4:20 pm
    Reply with quote

    Since Kolusu outlined how to do it in two steps, without ICETOOL, do you want to show us what you have?

    A COPY operation.

    OUTFIL with REMOVECC,NODETAIL and SECTIONS for your key, with MAX for each of your values.

    A second COPY operation, with INREC OVERLAY doing the additions.
    Back to top
    View user's profile Send private message
    mistah kurtz

    Active User


    Joined: 28 Jan 2012
    Posts: 316
    Location: Room: TREE(3). Hilbert's Hotel

    PostPosted: Tue Aug 13, 2013 6:55 pm
    Reply with quote

    I was trying the two step solution as suggested by Kolusu and Bill..just as a learning exercise..and posting the code here..hope this helps..feel free to share any suggestion.
    Code:
    //STEP01   EXEC PGM=SORT                                 
    //SORTIN   DD *                                         
    20130804 112101527R 10.12 14.02 24.14                   
    20130804 112101527R 15.00 14.00 29.00                   
    20130804 112740002R 07.00 09.00 16.00                   
    20130804 112740002R 08.00 14.00 24.00                   
    20130804 112740002R 08.00 11.00 18.00                   
    20130804 112748371R 01.01 02.02 03.03                   
    20130804 112749532R 00.97 02.03 03.00                   
    //SORTOUT  DD DSN=&&TEMPDD,DISP=(NEW,PASS),             
    //            SPACE=(TRK,(10,10),RLSE),DCB=*.SORTIN     
    //SYSOUT   DD SYSOUT=*                                   
    //SYSIN    DD *                                         
     OPTION COPY
     OUTFIL REMOVECC,NODETAIL,                               
            SECTIONS=(1,20,                                 
            TRAILER3=(1,20,MAX=(21,5,SFF,EDIT(TT.TT)),       
                      26,1,MAX=(27,5,SFF,EDIT(TT.TT))))     
    /*                                                       
    //STEP02   EXEC PGM=SORT                                 
    //SORTIN   DD DSN=&&TEMPDD,DISP=(OLD,DELETE)             
    //SORTOUT  DD SYSOUT=*                                   
    //SYSOUT   DD SYSOUT=*                                   
    //SYSIN    DD *                                         
     OPTION COPY                                             
     OUTREC BUILD=(1,32,(21,5,SFF,ADD,27,5,SFF),EDIT=(TT.TT))
    /*                                                       


    Output is:
    Code:
    20130804 112101527R 15.00 14.02 29.02
    20130804 112740002R 08.00 14.00 22.00
    20130804 112748371R 01.01 02.02 03.03
    20130804 112749532R 00.97 02.03 03.00
    Back to top
    View user's profile Send private message
    Bill Woodger

    Moderator Emeritus


    Joined: 09 Mar 2011
    Posts: 7309
    Location: Inside the Matrix

    PostPosted: Tue Aug 13, 2013 7:42 pm
    Reply with quote

    About how I thought it should be.
    Back to top
    View user's profile Send private message
    mistah kurtz

    Active User


    Joined: 28 Jan 2012
    Posts: 316
    Location: Room: TREE(3). Hilbert's Hotel

    PostPosted: Wed Aug 14, 2013 11:10 am
    Reply with quote

    Thanks Bill :-)
    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 How to load to DB2 with column level ... DB2 6
    No new posts RC query -Time column CA Products 3
    No new posts first column truncated in search result IBM Tools 13
    No new posts Split a record with data in a differe... DFSORT/ICETOOL 8
    No new posts Infosphere Optim - unable to save Col... IBM Tools 0
    Search our Forums:

    Back to Top