|
View previous topic :: View next topic
|
| Author |
Message |
AllyDick
New User
Joined: 07 Dec 2019
Posts: 5
Location: Ireland
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1429
Location: Bamberg, Germany
|
|
|
|
| Sample Output is missing Customer ID 7654212 that has duplicates as well. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1429
Location: Bamberg, Germany
|
|
|
|
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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1429
Location: Bamberg, Germany
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
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 |
|
|
AllyDick
New User
Joined: 07 Dec 2019
Posts: 5
Location: Ireland
|
|
|
|
| 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 |
|
|
AllyDick
New User
Joined: 07 Dec 2019
Posts: 5
Location: Ireland
|
|
|
|
| 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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1429
Location: Bamberg, Germany
|
|
|
|
| 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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
