Need to Remove the repeated values

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Hi All,
I Need to remove the repeated values, will explain you with an example

Example :

input File

PrNo Duplicates

3839 Duplicates of 3841 3856 3879
3840 No Duplicates
3841 Duplicates of 3839 3856 3879
3842 Duplicates of 3853 3865
3843 No Duplicates
......
3853 Duplicates of 3842 3865
3854 Duplicates of 3855
3855 Duplicates of 3854
3856 Duplicates of 3839 3841 3879
......
3865 Duplicates of 3842 3853
..........

Need an output file like this

Output File

PrNo Duplicates

3839 Duplicates of 3841 3856 3879
3840 No Duplicates
3842 Duplicates of 3853 3865
3843 No Duplicates
3854 Duplicates of 3855

The Record length of the file is 105 and Format FB

Size:

PrNo - length 5
Duplicates - length 34 (Txt(Duplicates of and No duplicates) is 14, Remaining 20 (it can hold max of 4 Duplicate PrNo))

dick scherrer · Posted: Tue Aug 12, 2008 7:40 pm

Hello,

Please clarify why this was considered a duplicate?

3841 Duplicates of 3839 3856 3879

I'd recommend a better description of the rules to go with the example files.

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Hi Dick,

The remaing fields in the input file has the same value, so it is considered as duplicates
example:

PrNO PoNo StoreNo Duplicates
3839 1234 9898 Duplicates of 3841 3856 3879
3841 1234 9898 Duplicates of 3839 3856 3879
3856 1234 9898 Duplicates of 3839 3841 3879
3879 1234 9898 Duplicates of 3839 3841 3856

so the PrNo which has the same PoNo and StoreNo are considered as duplicate one to other.

dick scherrer · Posted: Tue Aug 12, 2008 10:05 pm

Hello,

How did PoNo and StoreNo come into the equation?

You need to provide a much more clear definition of the "rules" you want implemented.

SOmeone may make a lucky guess, but it would be better to have a more clear descriptoin of what you want to do.

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Hi Dick,
sorry for late reply...

I am retrieving details from a table and if the set of PoNo StoreNo is present for more than one PrNo then in oputput i will write that one PrNo is duplicate to another, finally my output file is like below, in that i don't need a repeated values,

PrNO PoNo StoreNo Duplicates
3839 1234 9898 Duplicates of 3841 3856 3879
3841 1234 9898 Duplicates of 3839 3856 3879
3856 1234 9898 Duplicates of 3839 3841 3879
3879 1234 9898 Duplicates of 3839 3841 3856

Arun Raj · Posted: Thu Aug 14, 2008 7:23 pm

Hello,

dick scherrer · Posted: Thu Aug 14, 2008 11:51 pm

Hello,

I must be wearing my slow brain this week . . .

How were these records created? Why were they created? How is th output to be used?

Is it possible that the "duplicates" field need not even be used for this process? Is what is really needed the lowest PrNO of those records that all contain the same PoNo and StoreNo?

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Hi,

I will explain what my program does,

Table 1
Pr No
3839
3840
3841
3842
3853
3856
3865
3879

Table 2
Pr No PoNo StoreNo
3839 1234 9898
3840 1222 9899
3841 1234 9898
3842 2233 5566
3853 2233 5566
3856 1234 9898
3865 2233 5566
3879 1234 9898

First i will take first PrNo from Table 1 and check if it is present in Table 2 or not, If it is present then i'll take the corresponding PoNo, StoreNo of that PrNo from Table 2 and search if that pair of PoNo and Store no is present for any other PrNo (in table @ itself), if so i'll write that PrNo is duplicate of the first one (which i taken from Table 1), so this will continyue for all Prno present in Table 1.

for ex:

first i take 3839 from table 1 and search it in Table 2 and take corresponding PoNo and StoreNo and search if it is present for any other PrNo, same is present for 3856 and 3879, in the output i will write 3856 and 3879 are duplicates of 3839.

like wise for 3856 i'll write 3839 and 3879 are duplicates of 3856..

thanks
balukanna

Arun Raj · Posted: Mon Aug 18, 2008 7:16 pm

balukanna,

Are you using the file written in the above manner anywhere else?

If not, you could slightly change the logic of the code to write only the first PrNo,PoNo,StoreNo always if you have duplicates. Are the tables mentioned DB2 tables?

Thanks,
Arun

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Hi Arun,

The tables are DB2 tables only, i found difficult on changing the code to write only the first PrNo, PoNo, StoreNo when there is duplicates,
So i thought it will be done through sort.

Thanks
balukanna

Arun Raj · Posted: Mon Aug 18, 2008 7:49 pm

balukanna,

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Arun,

Your solution is possible, but the table consist of morethan a lakh of records,
if i found first duplicate and moved it to a WS_TABLE (ex:PrNo 31000) and i may get duplicate of that in (Prno 98000), there may be many combination of duplicate reside in between this, thats why i told i found difficult on changing the code.

Thanks
balukanna

Arun Raj · Posted: Mon Aug 18, 2008 8:13 pm

Garry Carroll · Posted: Mon Aug 18, 2008 8:48 pm

From what I think I'm seeing, how about....

Arun Raj · Posted: Mon Aug 18, 2008 8:59 pm

Check out the below ICETOOL job.

balukanna · New User Joined: 09 Apr 2008 Posts: 41 Location: USA

Hi Garry/Arun,

Thanks a lot... its working fine...

As i focussed to do sorting with the datas from Duplicates column, it seems little difficult, now with your solutions, doing sorting with the datas from PoNo,StoreNo, the work is made easier and giving the desired output..

Thanks
balukanna