Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to take the maximum value of two column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
srajanbose
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Tue Aug 06, 2013 9:49 am    Post subject: How to take the maximum value of two column
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

    DFSORT Moderator


    Joined: 09 Mar 2011
    Posts: 7236

    PostPosted: Tue Aug 06, 2013 12:28 pm    Post subject: Reply to: How to take the maximum value of two column
    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
    Warnings : 1

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 12:31 pm    Post subject:
    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

    DFSORT Moderator


    Joined: 09 Mar 2011
    Posts: 7236

    PostPosted: Tue Aug 06, 2013 12:36 pm    Post subject: Reply to: How to take the maximum value of two column
    Reply with quote

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

    Moderator


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

    PostPosted: Tue Aug 06, 2013 12:48 pm    Post subject:
    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
    Warnings : 1

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 12:48 pm    Post subject:
    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

    Moderator


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

    PostPosted: Tue Aug 06, 2013 12:59 pm    Post subject:
    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
    Warnings : 1

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 1:03 pm    Post subject:
    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

    Moderator


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

    PostPosted: Tue Aug 06, 2013 1:58 pm    Post subject:
    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
    Warnings : 1

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 06, 2013 2:03 pm    Post subject:
    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

    Moderator


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

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

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

    DFSORT Moderator


    Joined: 09 Mar 2011
    Posts: 7236

    PostPosted: Tue Aug 06, 2013 3:27 pm    Post subject: Reply to: How to take the maximum value of two column
    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    Post subject:
    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
    Warnings : 1

    New User


    Joined: 11 Oct 2004
    Posts: 69
    Location: chennai

    PostPosted: Tue Aug 13, 2013 3:43 pm    Post subject:
    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

    DFSORT Moderator


    Joined: 09 Mar 2011
    Posts: 7236

    PostPosted: Tue Aug 13, 2013 4:20 pm    Post subject: Reply to: How to take the maximum value of two column
    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: 268
    Location: Room: TREE(3). Hilbert's Hotel

    PostPosted: Tue Aug 13, 2013 6:55 pm    Post subject:
    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

    DFSORT Moderator


    Joined: 09 Mar 2011
    Posts: 7236

    PostPosted: Tue Aug 13, 2013 7:42 pm    Post subject: Reply to: How to take the maximum value of two column
    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: 268
    Location: Room: TREE(3). Hilbert's Hotel

    PostPosted: Wed Aug 14, 2013 11:10 am    Post subject:
    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    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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
    This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
    No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am
    No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
    No new posts Query using SUBSTRING on a column of ... sasanka DB2 5 Mon Jul 04, 2016 1:01 pm


    Facebook
    Back to Top
     
    Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us