Posted: Fri Jan 19, 2024 11:30 am Post subject: Identify and drop lower totals within a bundle.
Good day, I'd appreciate some help to accomplish the following.
There's a sequential dataset, RECFM=FB.
Each record has an Acc-no and Job-no values. There could be multiple records with the same Acc-no but different Job-no.
Job-no's within a single account are always unique.
Record layout:
Acc-no x(08).
Name x(24).
Address x(34).
Job-no x(04).
Task:
Need to identify if there's at least ONE duplicate Job-no accross different accounts, and if there is, calculate total of Job-no within each of those accounts, to keep the one with a highest total count to an Output, and drop the others with lower totals. If totals within an identified bundle happened to be identical, keep one of those, regardless of its place, and drop the others. Totals can go from 1 up to 999.
Also store all non-matching by Acc-no/Job-no records to an Output.
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Fri Jan 19, 2024 5:57 pm Post subject:
ramy2016 wrote:
Task:
Need to identify if there's at least ONE duplicate Job-no accross different accounts, and if there is, calculate total of Job-no within each of those accounts, to keep the one with a highest total count to an Output, and drop the others with lower totals.
ramy2016,
your requirement does NOT match with the output you show.
Why isn't Acc-no 00000100 not picked up? Same is the case with 00000400 Both of them have duplicates
Code:
Acc-No Name Address Job-no
00000100 1111
00000100 2222
00000400 3333
00000400 4444
00000400 6666
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
kolusu,
Acc 100 matched to Acc 300 through the Job-no 2222, but since Acc 300 had higher total of Job-no's, 4 vs 2, the Acc 100 got dropped. And the same goes for the Acc 400 which had matched to Acc 300 through at least one Job-no 333, but since Acc 300 had higher total of Job-no's, 4 vs 3, the Acc 400 didn't make it to an Output neither.
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Sat Jan 20, 2024 8:28 pm Post subject:
ramy2016,
Unfortunately achieving this using DFSORT would require more than 3 passes of data. Also the logic gets trickier if more than 1 duplicate job number matched with an acc-no. A program would be a better choice here. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Mon Jan 22, 2024 7:37 pm Post subject:
ramy2016,
As mentioned earlier, it takes about 4 passes to get the desired results.
Code:
//***************************************************************
//* Add the count of Acc-no to every record using Joinkeys *
//***************************************************************
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTDIAG DD DUMMY
//INA DD DISP=SHR,DSN=your.input.FB.71.byte file
//INB DD DISP=SHR,DSN=your.input.FB.71.byte file
/*
//SORTOUT DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,8,A) # ACC-NO
JOINKEYS F2=INB,FIELDS=(1,8,A) # ACC-NO
JOIN UNPAIRED
REFORMAT FIELDS=(F1:01,71, # Full lrecl
F2:09,05) # Space + Count
/*
//JNF2CNTL DD *
INREC BUILD=(01,08, # ACC-NO
X, # Space
C'0001') # Init count 1
SUM FIELDS=(10,04,ZD) # Count Sum
/*
//***************************************************************
//* Init every record with 'P' (Pick) and then tag the duplicate*
//* job-no with a space based on the count *
//* Sort the count on descending order so that highest acc-no *
//* is first in the list *
//***************************************************************
//STEP0200 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTDIAG DD DUMMY
//SORTIN DD DISP=SHR,DSN=&&T1
//SORTOUT DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//SYSIN DD *
SORT FIELDS=(67,04,CH,A, # Job-no
73,04,CH,D, # Count desc
01,08,CH,A),EQUALS # Acc-no
OUTREC IFOUTLEN=72,
IFTHEN=(WHEN=INIT,
OVERLAY=(72:C'P', # Pick Ind
80:SEQNUM,3,ZD, # Number the dup
RESTART=(67,4))), # on Job-no
IFTHEN=(WHEN=GROUP,
BEGIN=(80,3,ZD,EQ,1), # if dupcount=1
PUSH=(84:73,04)), # count
IFTHEN=(WHEN=(80,3,ZD,GT,1,AND, # if dupcount > 1
(84,04,ZD,GT,73,04,ZD)), # highcount > count
OVERLAY=(72:X)) # space out Pick ind
/*
//***************************************************************
//* Sort on the Acc-no and the pick indicator and since the *
//* Acc-no with less job count is tagged with a space it will *
//* be omitted. *
//***************************************************************
//STEP0300 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTDIAG DD DUMMY
//SORTIN DD DISP=SHR,DSN=&&T2
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(01,08,CH,A, # Acc-no
72,01,CH,A),EQUALS # Pick-ind
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum