eliminate duplicates with different data



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

eliminate duplicates with different data

Postby cobol_dev » Fri Jun 03, 2022 3:47 am

I was remembered to post my question in the right board. So my problem has something to do with sort or icetool and here we are.
See the attached images to my input and output data.Instead of displaying the records in its original format, because data could only be interpredet by using hex on in ispf editor i used a tool named insync with an overlay to make data visible.

Columns num_vnr, betrag and buschl are the columns to make my output data. To each num_vnr i have several records with buschl 20 (not only one as in this example), 100 (not only one as in this example) and 105. Take a look to the input dataset with the records with num_vnr equal to 82308036. you can see a record to buschl 20 with betrag (translation to english price or amount) +125,28. This ist the second record in input data set. The same num_vnr has, this is the third record, with buschl 105 the same value to betrag if you multiply it by -1. For me in this context these are siblings. Both lines (record 2 and record 3) should be eliminated. But remember, i can have several records wit the same buschl (20, 100, 105) and maybe i have also two or more records with buschl 20 and the same value to betrag and a different quantity to buschl 105. I have to found siblings. any other record must be in the ouput file. The record with buschl 100 must be also in output record and is not in focus to this task. Also it's not a condition that any record with buschl 105 is negative and any record with buschl 20 is positive. i don't have any idea to solve this with one sort or icetool step. think i have to do it in more than one step.

Hope my bad english is good enough to discribe my task?!
You do not have the required permissions to view the files attached to this post.
cobol_dev
 
Posts: 7
Joined: Wed Apr 13, 2022 11:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: eliminate duplicates with different data

Postby sergeyken » Fri Jun 03, 2022 11:01 pm

It is tricky, but possible.

It would take time to demonstrate; cannot do it now.
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 285
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 25 times

Re: eliminate duplicates with different data

Postby cobol_dev » Sat Jun 04, 2022 10:13 pm

tnx to your answer. the task has changed again. customer want all records. so no need to eliminate any. my own suggestions going this way

first cut input in one file with all records that not in the focus. to this task all with a value buschl not equal 20 and not equal 105.
also make another file with all records with that value 20 or value 105.
than make all values betrag positive. if you do so you can compare buschl 20 to buschl 105 with same value in betrag
all records not pairing going in another output file like all the records with buschl 100. both of these files have records we want after finishing the task
so we have records to analyze with buschl 20 and buschl 105 with the possibility of an unequal number. maybe 3 records with buschl 20 and betrag 10,05 and 2 records with the same value to field betrag and buschl 105.
but this last one step can be solved also.
i think we have to this in more than one step.
but i am a lucky man because the task changed :-)

so i wish a nice weekend :-)
cobol_dev
 
Posts: 7
Joined: Wed Apr 13, 2022 11:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: eliminate duplicates with different data

Postby sergeyken » Sun Jun 05, 2022 1:03 am

I've done your initial task, to demonstrate the method to be used.

You can make the changes as you are required to do so. Anyway, I'm not going to do your full task instead of you.

Due to the specific format of your input data, I had to add one extra step: //PREPARE - to generate test input file in your format from visible and printable test input data.
The real operations are performed in //SORTMISC step.

I do not have DFSORT/ICETOOL available right now, so you can replace one DD-name in the //SORTMISC step: from //SSMSG to //DFMSG. No other changes are expected when switching between SYNCSORT<->DFSORT utilities.

//*====================================================================
//PREPARE  EXEC PGM=SYNCSORT                                          
//*                                                                  
//SYSOUT   DD  SYSOUT=*                                              
//*                                                                  
//SYMNAMES DD  *                                                      
NUM_VNR,25,4,BI                                                      
BUDAT,37,10,CH                                                        
BETRAG,47,9,ZD                                                        
BUSCHL,59,2,BI                                                        
IN_NUM,1,8,ZD                                                        
IN_BUD,10,10,CH                                                      
IN_BET,21,11,SFF                                                      
IN_BUS,33,3,SFF                                                      
//*                                                                  
//SORTIN   DD  *                                                    
00000001 1111-11-11 -1111111.22 20                                  
82116403 2019-01-20 -0000061.69 20                                  
82308036 2019-03-20 +0000125.28 20                                  
82308036 2019-02-20 +0000125.28 20                                    
82308036 2019-01-20 +0000125.28 20                                  
82308036 2019-12-01 -0000125.28 105                                  
83669658 2019-01-20 +0000402.80 20                                  
84304455 2019-12-01 +0000204.00 100                                  
85601898 2019-12-01 +0001279.32 100                                  
85623651 2019-01-20 -0000044.40 20                                  
85623651 2019-12-01 +0000044.40 105                                  
85623651 2019-01-20 +0000444.40 20                                  
85623651 2019-01-20 +0000444.40 20                                  
85623651 2019-12-01 -0000444.40 105                                  
85623651 2019-01-20 +0044444.40 100                                  
85770501 2019-01-20 +0000018.00 20                                  
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7
//*                                                                  
//SORTOUT  DD  DISP=(NEW,PASS),DSN=&&REALDATA,                      
//             SPACE=(TRK,(10,10),RLSE)                              
//*                                                                  
//SYSIN    DD  *                                                      
 INREC BUILD=(NUM_VNR:IN_NUM,TO=BI,LENGTH=4,                          
              BUDAT:IN_BUD,                                          
              BETRAG:IN_BET,TO=ZDC,LENGTH=9,                          
              BUSCHL:IN_BUS,TO=BI,LENGTH=2,                          
              80:X)                                                  
 SORT FIELDS=COPY                                                      
//*                                                                  
//*====================================================================
//SORTMISM EXEC PGM=SYNCTOOL,COND=(0,NE)                              
//*                                                                    
//SYMNAMES DD  *                                                      
NUM_VNR,25,4,BI                                                      
BUDAT,37,10,CH                                                        
BETRAG,47,9,ZD                                                        
BUSCHL,59,2,BI                                                        
RECIN,1,80                                                            
RECEND,*                                                              
*                                                                    
REBETRAG,*,9,ZD                                                      
SKIP,1                                                                
RENUM,*,8,ZD                                                          
SKIP,1                                                                
REBUSCHL,*,3,ZD                                                      
SKIP,1                                                                
SEQNO,*,5,ZD                                                          
*                                                                    
POSITION,RECEND                                                      
IDKEY,*,28,BI                                                        
*                                                                    
//*                                                                    
//TOOLMSG  DD  SYSOUT=*                                              
//SSMSG    DD  SYSOUT=*                                              
//SORTIN   DD  DISP=(OLD,DELETE),DSN=&&REALDATA                      
//*                                                                    
//REC105   DD  SPACE=(TRK,(20,20))                                    
//RECS     DD  SPACE=(TRK,(20,20))                                    
//DEBUG    DD  SYSOUT=*                                              
//*                                                                  
//SORTOUT  DD  SYSOUT=*                                              
//*....................................................................
//TOOLIN   DD  *                                                      
 SORT FROM(SORTIN)  TO(RECS,REC105)  USING(SPLT)                      
 COPY JKFROM        TO(SORTOUT)      USING(SEPR)                      
//*....................................................................
//SPLTCNTL DD  *                                                      
 INREC IFTHEN=(WHEN=INIT,                                            
       BUILD=(1,80,                                                  
              REBETRAG:BETRAG,MUL,-1,TO=ZDC,LENGTH=9,                
              RENUM:NUM_VNR,TO=ZDF,LENGTH=8,                          
              REBUSCHL:BUSCHL,TO=ZDF,LENGTH=3)),                      
       IFTHEN=(WHEN=(BUSCHL,NE,+105),                                
       OVERLAY=(REBETRAG:BETRAG,TO=ZDC,LENGTH=9))                    
 SORT FIELDS=(NUM_VNR,A,                                              
              REBETRAG,A)                                            
 OUTREC IFTHEN=(WHEN=GROUP,                                          
                KEYBEGIN=(IDKEY),                                      
                PUSH=(SEQNO:SEQ=5))                                  
 OUTFIL FNAMES=(REC105),                                      
        INCLUDE=(BUSCHL,EQ,+105)                                      
 OUTFIL FNAMES=(RECS),                                          
        INCLUDE=(BUSCHL,NE,+105)                                      
//*                                                                  
//SEPRCNTL DD  *                                                      
 JOINKEYS F1=RECS,                                                    
          FIELDS=(BETRAG,A,        REAL AMOUNT                        
                  RENUM,A,         ACCOUNT NUMBER                    
                  SEQNO,A)         DUPLICATE NUMBER                  
 JOINKEYS F2=REC105,                                                  
          FIELDS=(REBETRAG,A,      INVERTED AMOUNT                    
                  RENUM,A,         ACCOUNT NUMBER                      
                  SEQNO,A)         DUPLICATE NUMBER                  
 JOIN UNPAIRED,F1,ONLY                                                
 REFORMAT FIELDS=(F1:RECIN)                                          
//*                                                                    
//*====================================================================
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 285
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 25 times

Re: eliminate duplicates with different data

Postby sergeyken » Wed Jun 15, 2022 7:28 pm

Remains unclear: why people are posting their questions (rather: their requirements to do their job instead of them), and later are never interested in reading, or understanding the response? :?

Bleibt unklar: Warum posten die Leute ihre Fragen (eher: ihre Anforderungen, um ihre Arbeit zu erledigen, anstatt sie) und sind später nie daran interessiert, die Antwort zu lesen oder zu verstehen? :?
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 285
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 25 times

Re: eliminate duplicates with different data

Postby cobol_dev » Wed Jun 15, 2022 9:16 pm

I don't understand your input. You should follow the timeline. Between the first question and the last solution is a change in the task and i told this in my answer. if you want a discussion to this behaviour we should do it via pm. this should not be discussed in this thread. it's not topic. it's only a opinion... and more i will not answer in this thread.

one more:
it's great if you don't give up. but as i said task is changed and i'm in a business with very, very short time. between your first answer and your last answer i have many, many, many other things to do and so, sorry about, i can't answer to all my questions. but your welcome and i can save your solution if i have a same task in future... but i don't know when this case will be activated?!?!?!

you are in retirement, don't you? it's hard to be a mainframe developer in a world where day by day developers and there know how is gone of going to retirement... so yes, great to find a forum, but job is very, very hard.

maybe you can imagine it?

sergeyken wrote:Remains unclear: why people are posting their questions (rather: their requirements to do their job instead of them), and later are never interested in reading, or understanding the response? :?

Bleibt unklar: Warum posten die Leute ihre Fragen (eher: ihre Anforderungen, um ihre Arbeit zu erledigen, anstatt sie) und sind später nie daran interessiert, die Antwort zu lesen oder zu verstehen? :?
cobol_dev
 
Posts: 7
Joined: Wed Apr 13, 2022 11:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: eliminate duplicates with different data

Postby sergeyken » Wed Jun 15, 2022 10:07 pm

I have to repeat again:

I've presented you an example of the general approach to tasks like yours. The approach itself is absolutely the same, either for your initial requirements, or for newly changed ones. In case you understood how the approach is working, you might easily update the example according to your "changed requirements" - it requires so minor updates, that it's a shame to start talking about them.

Instead, you want the whole job, from top to bottom, to be done in full for you. It is very indecent at forums like this one.

I spent some time trying to carefully present you the general solution, which really might be not obvious for beginners.
Instead of any gratitude, you decided to blame on "not giving you the final code in full". Why do you think that you are a more busy person than others, who tried to help you?

If so, I also stop responding to this frustrating thread.

P.S.
I'm not retired yet, and have to do my own job, besides helping others.
Otherwise I would not be able to test the presented solution before presenting it to you.
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 285
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 25 times

Re: eliminate duplicates with different data

Postby cobol_dev » Wed Jun 15, 2022 10:44 pm

i'm sorry about to hear... mybe some lost in translation? english isn't my mother tongue. i don't want upsate you. so mea culpa... sorry that i'm wrong you in retirement. i thought you are the programmer of icetool or dfssort. In this case i thought, dam'n it's great, i got an answer of the programmer of this ibm tools and he do it in his retirement.

so again... sorry about if you don't honored as you are you habe been used to in the past (sorry, my english isn't well)....

I can't explain it very well. sorry about. i only wan't you to know that i was grateful to the first answer and i don't need any more support because the task is changed ( i do not have to change the input file in any case. mybe this isn't clear to you). But you go the whole way. tnx, but as i said it was no longer the task.

Maybe i had to say: sorry, i don't know any answer because the question is deleted, because customer want the input file as it is?

i don't know how i can explain it better.... so don't got an answer for the whole way. it wasn't need further? how can i explain it better? i don't know :-(

i can't say more than: mea culpa....

Maybe this answer is also wront interpreted, because i can't explain it in english very well. ...
cobol_dev
 
Posts: 7
Joined: Wed Apr 13, 2022 11:48 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post