Duplicate Records

praburaj · Post by **praburaj** » Fri Jul 28, 2006 2:18 am

Hai,

I have one problem. I need to check duplicate record from two column.

If it is duplicate record, that record store in to Error Table.

Ex:

Name Age

Raj 25

kamal 45

vinodh 40

Raj 25

Now , the dublicate record is Raj. This Raj record is send to the Error

Table.

How can i do that. Can any one help me?.

Regards,

prabu

loveojha2 · Post by **loveojha2** » Fri Jul 28, 2006 2:25 am

You can do it using
the sorted data and checking for duplicates using the stage variables within the transformer.

ramudwhelp · Post by **ramudwhelp** » Fri Jul 28, 2006 2:38 am

Use Aggregator Stage and make group by on both columns.

kumar_s · Post by **kumar_s** » Fri Jul 28, 2006 2:40 am

Perform a search on this forum, this has been discussed many times.
Search for "How do I remove duplicate rows from a data source?" in FAQ.

praburaj · Post by **praburaj** » Fri Jul 28, 2006 2:55 am

My condition is that dupicate records store in to the Error Table (

Target_Sequential file) help me the query. If i use Aggregator Stage, is

it show the duplicate records separately. Otherwise, if any condition for

this, can u . I need two check these two colums AcctCode and DeptCode

give me the query.

Regards,

prabu

loveojha2 · Post by **loveojha2** » Fri Jul 28, 2006 3:07 am

No need to go for aggregator stage.

Sort the data first (based on the two columns).

Then check the duplicates through the stage variables if found pass it to the error table link else to the target link.

praburaj · Post by **praburaj** » Fri Jul 28, 2006 3:26 am

Once i sort the data, that duplicate records will be removed. I don't

want. I need send Duplicate Record in to target link( Error_Target). Can

u give me the query

loveojha2 · Post by **loveojha2** » Fri Jul 28, 2006 3:35 am

How are you sorting the data?

You can use the sort stage for the puppose, it won't remove the duplicates.

praburaj · Post by **praburaj** » Fri Jul 28, 2006 3:48 am

Ok, Now, I am using the Sort Stage I sort the data. Then How can i write

query in Transformer. I know that , create Stage Variable. My

Comparing column in Acct Code, DeptCode. Can u help me the query.

praburaj · Post by **praburaj** » Fri Jul 28, 2006 3:48 am

Ok, Now, I am using the Sort Stage I sort the data. Then How can i write

query in Transformer. I know that , create Stage Variable. My

Comparing column in Acct Code, DeptCode. Can u help me the query.

loveojha2 · Post by **loveojha2** » Fri Jul 28, 2006 4:02 am

You don't write a query in transformer. Its the place where you do the transformation.

Take two stage variables
Prev_Row
Curr_Row

Set Curr_Row to Col1 : Col2
Constraint for the Target Link
Curr_Row<>Prev_Row
Constraint for the Error Link
Curr_Row=Prev_Row

Then set
Prev_Row=Curr_Row

It should give you the outline of how you should proceed.

praburaj · Post by **praburaj** » Fri Jul 28, 2006 4:59 am

i tried ur ida. But it is not work. I tried Like that.

PrevRow -----------------> AccCode

CurrRow ------------------> DeptCode

Then Set in constraints

Error Records:

CurrRow=PrevRow

Good Records:

CurrRec<>PrevRec

In Error Records Link did not show any records. It is showing only Zero

Records. Can u help me other one.

loveojha2 · Post by **loveojha2** » Fri Jul 28, 2006 5:20 am

Please read my earlier post again, I asked you to use derivation of Curr_Row to concate of the AccCode and DeptCode. i.e.
Set Curr_Row to AccCode : DeptCode

praburaj · Post by **praburaj** » Fri Jul 28, 2006 5:34 am

Hai lovejo,

I also tried what u said. But still it is not work.

what i did there. (Acc Code and Dept Code)

Stage Variable:

Prev Row--------------------> Acc Code

Curr Row---------------------> Acc Code : Dept Code

In Constraints:

Good Records:

CurrRow<> PrevRow

Error Records:

CurrRow = PrevRow

I tried what u said. But still it show zero record in Error Table. I dont

know that why it is show. If i did any mistake, can u help me where i did

make mistake and How can i correct it. plz Help me.

balajisr · Post by **balajisr** » Fri Jul 28, 2006 6:24 am

praburaj wrote:Hai lovejo,

I also tried what u said. But still it is not work.

what i did there. (Acc Code and Dept Code)

Stage Variable:

Prev Row--------------------> Acc Code

Curr Row---------------------> Acc Code : Dept Code

In Constraints:

Good Records:

CurrRow<> PrevRow

Error Records:

CurrRow = PrevRow

I tried what u said. But still it show zero record in Error Table. I dont

know that why it is show. If i did any mistake, can u help me where i did

make mistake and How can i correct it. plz Help me.

Prabhu,

As lovejha mentioned have two stage variables.

1.IsReject ---> if link.AccCode:link.DeptCode = PrevRow Then 1 Else 0
2.PrevRow----> link.AccCode:link.DeptCode

In the error link constraints:
IsReject = 1

In the other link:
IsReject = 0