How to handle duplicate records in ezytrieve..

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Hi,

I am using 2 different input files in my ezytreive job where I have used Customer_number as matching field. But in my job 1st input file will not have duplicates but 2nd input files is having duplicates. As per my knowledge easytrieve will not process duplicate records in the 2nd file and same is happening for me too. But i want to process duplicate records in the 2nd file and write to report based on my condition.

I have searched all over net but didn't find any solution for processing duplicate record. I did come across the condition IF DUPLCIATES but not sure how it will help me out.

Can anyone please guide me to resolve this. Even I searching for manuals from long.

Thanks,
Chetan

dick scherrer · Posted: Sat Apr 28, 2012 9:43 am

Hello and welcome to the forum,

Consider using your file2 as file1 and your file1 as file2. . .

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Dick,

I have tried using file2 as file1 and file1 as file2 but still I am not getting expected results. Duplicates in the 1st file are also not getting processed.

Thanks,
Chetan

Bill Woodger · Posted: Sat Apr 28, 2012 1:03 pm

If you have duplicates on both files, the "synchronised file processing" gets tricky.

If you can't get rid of duplicates on one of the files, then I'd recommend your own two-file match (there is a "sticky" in the Cobol forum with logic) or "sideways match".

Perhaps if you can explain how your processing should operate, with respect to the presence/absence of keys and duplicates on either file.

Also, knock yourself up a couple of "test files" (they can be DD *) so you can understand what the synchronised processing is doing to your sample data. Keep it very simple.

PeterHolland · Posted: Sat Apr 28, 2012 2:44 pm

I used something like this :

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Bill,

As I said earlier I have 2 files with below details in it:
File1 contains Customer details (like name, adress, SSN etc)
File2 contains Customer transaction details (like purchases, payments etc)
Customer number is the matching field in both the files. File1 will not contain duplicates but File2 is having dupilcates. I want to process all the duplicate records in the File2 for corresponding record in File1.

My Ezytrieve job not at all processing duplicate record whether you use duplicate file as 1st file or 2nd file. Either way it is not processing duplicate records.

This is how my files looks like...

File1:
Cust Name City
------ ------- --------
0011 Chetan Mysore
0012 Bill Portugal
etc...

File2:
Cust Item Amount($)
----- ------ ------------
0011 Pant 100.00
0011 Shirt 150.00
0012 Tshirt 300.00
0012 Bag 350.00
0012 Shoes 300.00
etc..

Thanks,
Chetan

Bill Woodger · Posted: Sat Apr 28, 2012 3:01 pm

Bill Woodger · Posted: Sat Apr 28, 2012 4:14 pm

PeterHolland · Posted: Sat Apr 28, 2012 9:52 pm

Bill,

its just an example to process duplicates, in this case for the first file. It shouldnt be that difficult to do the same for the other file.

dick scherrer · Posted: Sat Apr 28, 2012 10:41 pm

Hello,

Understanding that the "knowledge" was incorrect, i suggested "flipping" the files.

Easytrieve (and the 2-file match/merge from the sticky) need special care when processing a many-to-many situation. If the data has only duplicates in one file, it should not be a problem.

If you post the code related to the "match", we may be able to offer a suggestion.

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Hi all,

Out of 2 files only one file is having duplicates. I have tried using duplicate file as 1st file and 2nd file but still not getting correct results.

a) with duplicate file as 1st file (SCAFILE):

Bill Woodger · Posted: Sat Apr 28, 2012 11:29 pm

Have you got some sample data to go with it please? Relevant filelds. Input files, output you are getting, output you expect,

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Bill,

Your are right!!..

I have used file with duplicates as 2nd file in my job and now i am getting correct results. I have used below code:

Bill Woodger · Posted: Sun Apr 29, 2012 2:42 pm

Well, I'm glad it is working if you are sure it is :-)

Don't forget that Dick pointed you this way, twice, as well and that Peter contributed an outline for duplicates on the first file.

I don't see how the matching code you are showing now is logically different from what you were showing for duplicates on file 2 before. I also can't see how the logic you had for matching the duplicates on the first file, along the same lines that Peter showed, would not work with the small sample of data that you showed.

I was thinking it had to be an error in setting up the output values which made you think the matching didn't work because of your previous "knowledge" :-)

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Thanks to Dick, Peter and to everyone!!..

Bill,

The main difference in logic from the code which i showed before and from the which worked out correctly is i didnt use IF MATCHED before. You can have a look at my before logic and logic which worked. You ll get the difference.

After trying all possible ways n searching over internet I thought Ezy wont handle duplicates. Hence I came to my false "knowledge"

Thanks,
Chetna

Bill Woodger · Posted: Sun Apr 29, 2012 5:41 pm

You used IF MATCHED before, you just negated it to exclude unmatched from getting any further.

dick scherrer · Posted: Mon Apr 30, 2012 12:48 am

Hello,

Good to hear it is working - thank you for letting us know

Suggest really thorough testing to make sure it works for all cases. It sounds like there may still be "opportunitites".

Jose Mateo · Posted: Mon Apr 30, 2012 7:46 pm

Good day, Mr. scherrer!

In Easytrieve, if you are doing synchronized file processing you could bypass duplicate records by testing the conditions DUPLICATE, FIRST-DUP or LAST-DUP on the same file by key but Easytrieve does process duplicate records if the coding is not bypassing those records.

dick scherrer · Posted: Mon Apr 30, 2012 9:12 pm

Hi Jose,

Yup, hopefully chetanambi now understands this

d

chetanambi · New User Joined: 21 Jan 2012 Posts: 58 Location: India

Hello everyone,

Now I really understand the how duplicates will be processed in Ezytrieve. My "knwoledge" was wrong indeed

.

Duplicates will be processed unless we skip it using DUPLICATES, FIRST-DUP and LAST-DUP as Jose mentioned. But need to use MATCHED and NOT MATCHED in correct place. Because wrong use of MATCHED was giving incorrect results for me.

Thank you all..

Regards,
Chetan

lonerusher · New User Joined: 30 Nov 2010 Posts: 4 Location: India

1. Driver File primary key(means first file primary in Job Input) should always have unique records if you are using key in Job input. If your driver file do not have unique records you need to declare the Job Input as Null and need to handle the file balancing logic using the DO WHILE logic.

2. Secondary File in Job Input : In secondary file its optional that you will always have unique records means it can have duplicate records for the primary key. Now if you want those duplicate elements in your output file just simply use job input and use if matched logic.
If you don't want duplicates into your output file, then you need to handle that using Job input NULL and do while. Alternatively you can do that using flag logic like storing the primary key into some temp variable and always comparing the new value and last value and then putting your logic.

3. If you are not sure about your input files always use JOB INPUT file as NULL and handle the files using DO while logic.

Enjoy

! hope this helps you in future..