Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Complex counting and grouping

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Complex counting and grouping
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

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Sun Mar 22, 2020 11:11 pm    Post subject:
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

Active User


Joined: 15 Aug 2015
Posts: 156
Location: Germany

PostPosted: Mon Mar 23, 2020 6:07 am    Post subject:
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

Active User


Joined: 15 Aug 2015
Posts: 156
Location: Germany

PostPosted: Wed Mar 25, 2020 1:42 am    Post subject:
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

Active User


Joined: 15 Aug 2015
Posts: 156
Location: Germany

PostPosted: Wed Mar 25, 2020 3:14 am    Post subject:
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

Active Member


Joined: 29 Apr 2008
Posts: 719
Location: Maryland

PostPosted: Wed Mar 25, 2020 4:12 pm    Post subject:
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    Post subject:
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    Post subject:
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

Active User


Joined: 15 Aug 2015
Posts: 156
Location: Germany

PostPosted: Mon Mar 30, 2020 9:23 pm    Post subject:
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    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 partitioning row-num and aggregation ... Auryn DB2 0 Wed Nov 06, 2019 2:46 am
No new posts How to summarize data by grouping on ... santhosh5983 DFSORT/ICETOOL 6 Tue Sep 04, 2018 9:07 am
No new posts How to include a particular field val... sakrat DFSORT/ICETOOL 8 Fri Apr 06, 2018 8:27 pm
No new posts Grouping of records trushant.w DFSORT/ICETOOL 8 Mon Jan 20, 2014 12:16 pm
No new posts Inserting new records based on grouping mohitsaini DFSORT/ICETOOL 1 Thu Sep 19, 2013 5:03 pm

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