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

Complex counting and grouping
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
AllyDick

New User


Joined: 07 Dec 2019
Posts: 5
Location: Ireland
PostPosted: Sun Mar 22, 2020 9:25 pm
 Reply with quote
Apologies if in the wrong forum.

Can I please get help with this problem.

File layout FB - 1200

Rec Types:
Client ID - 8 char
Account Number - 8 char
Customer ID - 7 char

Primary grouping is Customer ID.
There can be multiple Account Numbers per Customer ID
There can be multiple Client IDs per Account Number

Sample file:
Code:
Client ID   Account Number  Customer ID
1234567     12345678        2233445
2345678     12345678        2233445
1234567     23456789        2233445
1122334     98765432        3845123
2345698     12456890        1236789
3477890     12456890        1236789
1568973     17892243        1781112
4588776     23675421        1781112
1862727     11223344        9876543
1868222     11223344        9876543
1862727     11334455        9876543
1868222     11334455        9876543
2786542     36727262        7654212
2865252     36727262        7654212
2786542     56262626        7654212


First only show rows where Customer ID count is > 2
e.g. result should look like this:

Code:
Client ID   Account Number  Customer ID
1234567     12345678        2233445
2345678     12345678        2233445
1234567     23456789        2233445
1862727     11223344        9876543
1868222     11223344        9876543
1862727     11334455        9876543
1868222     11334455        9876543
2786542     36727262        7654212
2865252     36727262        7654212
2786542     56262626        7654212
1265252     17626262        1727272
1277818     17626262        1727272
1287282     17626262        1727272


Now count the Client IDs per Customer ID e.g.

Code:
Client ID  Count Account Number  Customer ID
1234567  1       12345678            2233445
2345678  1       12345678            2233445
1234567  2       23456789            2233445
1862727  1       11223344            9876543
1868222  1       11223344            9876543
1862727  2       11334455            9876543
1868222  2       11334455            9876543
1265252  1       17626262            1727272
1277818  1       17626262            1727272
1287282  1       17626262            1727272

Only include rows where there is a Client ID which has more than one instance per
Customer ID e.g. show Client ID 1234567 and 2345679 even though 2345678 only has 1 instance per Customer ID

Code:
Client ID  Count Account Number  Customer ID
1234567  1       12345678            2233445
2345678  1       12345678            2233445
1234567  2       23456789            2233445
1862727  1       11223344            9876543
1868222  1       11223344            9876543
1862727  2       11334455            9876543
1868222  2       11334455            9876543


Is it possible to do this? Would it be possible to do with ICETOOL or should I go with Easytrieve?

The steps I am showing are not necessary really. Just the end result is what I want to get to.

Many thanks
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2022
Location: USA
PostPosted: Sun Mar 22, 2020 11:11 pm
 Reply with quote
1) please, use code tags

2) what prevents you from doing what is required?

3) what did you try so far?

4) which part is considered as “complex grouping and counting?”
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1252
Location: Bamberg, Germany
PostPosted: Mon Mar 23, 2020 6:07 am
 Reply with quote
Sample Output is missing Customer ID 7654212 that has duplicates as well.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1252
Location: Bamberg, Germany
PostPosted: Wed Mar 25, 2020 1:42 am
 Reply with quote
Sample for Lengths Client(7) Account(8) Customer(7)
Code:
//ICETOOL  EXEC PGM=ICETOOL
//SORTIN   DD *                                                         
;                                                                       
;Print All Customer Data when it has duplicate Client Data
;                                                                       
;---+----1----+----2----+----3----+----4----+----5----+----6----+----7--
;Client Account  Customer                                               
1234567 12345678 2233445                                               
2345678 12345678 2233445                                               
1234567 23456789 2233445                                               
1122334 98765432 3845123                                               
2345698 12456890 1236789                                               
3477890 12456890 1236789                                               
1568973 17892243 1781112                                               
4588776 23675421 1781112                                               
1862727 11223344 9876543                                               
1868222 11223344 9876543                                               
1862727 11334455 9876543                                               
1868222 11334455 9876543                                               
2786542 36727262 7654212                                               
2865252 36727262 7654212                                               
2786542 56262626 7654212                                               
/*                                                                     
//STP0OUT  DD DISP=(NEW,PASS),UNIT=SYSALLDA,                       
//            SPACE=(CYL,(2,1),RLSE),                             
//            DSORG=PS,RECFM=FB,LRECL=80,BLKSIZE=0                 
//STP1OUT  DD DISP=(NEW,PASS),UNIT=SYSALLDA,                       
//            SPACE=(CYL,(2,1),RLSE),                             
//            DSORG=PS,RECFM=FB,LRECL=80,BLKSIZE=0                 
//SORTOUT  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//TOOLMSG  DD SYSOUT=*                                             
//TOOLIN   DD *                                                   
  COPY FROM(SORTIN) USING(STP0)                                   
  COPY FROM(STP0OUT) USING(STP1)                                   
  COPY FROM(STP1OUT) USING(STP2)                                   
/*                                                                 
//STP0CNTL DD *                                                   
  OPTION EQUALS                                                   
  OMIT COND=(1,1,CH,EQ,C';')                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(26:SEQNUM,7,ZD,50:18,7,1,7))   
  SORT FIELDS=(18,7,CH,A,1,7,CH,A)                                 
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(34:SEQNUM,7,ZD,RESTART=(18,7),
    42:SEQNUM,7,ZD,RESTART=(50,14)))                               
  OUTFIL FNAMES=(STP0OUT),                                         
    BUILD=(1,80)                                                   
  END                                                             
/*                                                                 
//STP1CNTL DD *                                           
  SORT FIELDS=(18,7,CH,A,42,7,CH,D)                       
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(18,7),PUSH(34:42,7))
  OUTFIL FNAMES=(STP1OUT),                               
    INCLUDE=(34,7,ZD,GT,+1),                             
    BUILD=(1,80)                                         
  END                                                     
/*                                                       
//STP2CNTL DD *                                           
  SORT FIELDS=(26,7,ZD,A)                                 
  OUTFIL FNAMES=(SORTOUT),                               
    BUILD=(1,7,X,42,7,ZD,M10,X,9,7,X,18,7)               
  END                                                     
/*

Output:
Code:
1234567       1 1234567 2233445
2345678       1 1234567 2233445
1234567       2 2345678 2233445
1862727       1 1122334 9876543
1868222       1 1122334 9876543
1862727       2 1133445 9876543
1868222       2 1133445 9876543
2786542       1 3672726 7654212
2865252       1 3672726 7654212
2786542       2 5626262 7654212
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1252
Location: Bamberg, Germany
PostPosted: Wed Mar 25, 2020 3:14 am
 Reply with quote
Just noticed that the Account is cut in Output. Replace BUILD= in STP2CNTL as follows:
Code:
BUILD=(1,7,X,42,7,ZD,M10,X,9,8,X,18,7)

Output:
Code:
1234567       1 12345678 2233445
2345678       1 12345678 2233445
1234567       2 23456789 2233445
1862727       1 11223344 9876543
1868222       1 11223344 9876543
1862727       2 11334455 9876543
1868222       2 11334455 9876543
2786542       1 36727262 7654212
2865252       1 36727262 7654212
2786542       2 56262626 7654212
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2022
Location: USA
PostPosted: Wed Mar 25, 2020 4:12 pm
 Reply with quote
There was no sign of trying to learn, or to understand something from TS side.

I'm pretty sure he's going to copy the example above into his project, to report "job done" to his manager, and to start new topic here, with "the new requirements received".
Back to top
View user's profile Send private message
AllyDick

New User


Joined: 07 Dec 2019
Posts: 5
Location: Ireland
PostPosted: Mon Mar 30, 2020 1:42 am
 Reply with quote
sergeyken wrote:
There was no sign of trying to learn, or to understand something from TS side.

I'm pretty sure he's going to copy the example above into his project, to report "job done" to his manager, and to start new topic here, with "the new requirements received".


You haven't seen the hours that I have slaved over this looking for a solution. Yes, I will take ideas from the solution that was kindly given to me by Joerg, and am happy to share whatever I know with others.
Back to top
View user's profile Send private message
AllyDick

New User


Joined: 07 Dec 2019
Posts: 5
Location: Ireland
PostPosted: Mon Mar 30, 2020 9:17 pm
 Reply with quote
Joerg.Findeisen wrote:
Just noticed that the Account is cut in Output. Replace BUILD= in STP2CNTL as follows:
Code:
BUILD=(1,7,X,42,7,ZD,M10,X,9,8,X,18,7)

Output:
Code:
1234567       1 12345678 2233445
2345678       1 12345678 2233445
1234567       2 23456789 2233445
1862727       1 11223344 9876543
1868222       1 11223344 9876543
1862727       2 11334455 9876543
1868222       2 11334455 9876543
2786542       1 36727262 7654212
2865252       1 36727262 7654212
2786542       2 56262626 7654212


I really appreciate your help with this. Many thanks
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1252
Location: Bamberg, Germany
PostPosted: Mon Mar 30, 2020 9:23 pm
 Reply with quote
It must have worked I guess? It's like Sudoku when I can't get no sleep. Just add the JCL as requested to make it easier for us to help you.
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 Grouping by multiple headers DFSORT/ICETOOL 7
No new posts How to give complex condition in JCL . CLIST & REXX 30
No new posts partitioning row-num and aggregation ... DB2 0
No new posts How to summarize data by grouping on ... DFSORT/ICETOOL 6
No new posts How to include a particular field val... DFSORT/ICETOOL 8
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us