veeraraju.v

• Mar 28th, 2006

 

Hi,

use this sequence to get the result.

source--->sq--->exp-->sorter(with enable select distinct check box)--->t1

                            --->aggregator(with enabling group by and write count 

function)--->t2

 

 

veeraraju.v

• Mar 29th, 2006

 

hi,

if u want any information for that question

call me to 9840559828.  or  mail to

veeraraju_v@yahoo.co.in

veeraraju.v

• Mar 29th, 2006

 

source--->sq--->exp-->sorter(with enable select distinct check box)--->t1

                            --->aggregator(with enabling group by and write count 

function)--->t2

If u want only duplicates to t2 u can follow this sequence

                             --->agg(with enable group by write this code decode(count(col),1,1,0))--->Filter(condition is 0)--->t2.

for any information call me to +91-0-9840559828///veeraraju_v@yahoo.co.in

gazulas

 Profile Answers by gazulas Questions by gazulas

• Apr 2nd, 2006

 

take two source instences and in first one embeded distinct in the source qualifier and connect it to the target t1.

 and just write a query in the second source instance to fetch the duplicate records and connect it to the target t2.

<< if u use aggregater as suggested by my friend u will get duplicate as well as distinct records in the second target >>

sm1506

• Apr 7th, 2006

 

This is not a right approach friends. There is a good practice of identifying duplicates. Normally when you ask someone how to identify a duplicate record in informatica, they say "Use aggregator transf". well you can just get a count from this, but not really identify which record is a duplicate. If it is RDBMS, you can simply write a query "select ... from ...group by <key fields> having count(*) > 1.

great! But what if the source is a flat file? you can use an aggregate and get the count of it. then you will filter and wanted to make sure it reacheds the T1 and T2 tgt's appropriately.

This would be the easiest way.

Use a sorter transformation. sort on key fields by which u want to find the duplicates. then use an expression tranformation.

Example:

Example:
field1-->
field2-->

SORTER:
field1 --ascending/descending
field2 --ascending/descending

Expression:
--> field1
--> field2

<--> v_field1_curr = field1
<--> v_field2_curr = field2
v_dup_flag = IIF(v_field1_curr = v_field1_prev, true, false)
o_dup_flag = IIF(v_dup_flag = true, 'Duplicate', 'Not Duplicate'

<--> v_field1_prev = v_field1_curr
<--> v_field2_prev = v_field2_curr

use a Router transformation and put o_dup_flag = 'Duplicate' in T2 and 'Not Duplicate' in T1.

Informatica evaluates row by row. So as we sort, all the rows come in order and it will evaluate based on the previous and current rows. hope its clear.

Adil M

• Jul 4th, 2007

 

Use a router transformation

d_sherry

 Profile Answers by d_sherry 

• Apr 8th, 2008

 

This can be done using an aggregator and a router. In the aggregator group by all columns and in the router give count(*)>=1 for the first group condition and connect it to T1 and give count(*) >1 for the second group condition and connect it to T2. This way you will get all the Uniq values in T1 and the ones which were duplicated to T2.

sri.kal

 Profile Answers by sri.kal Questions by sri.kal

• Apr 10th, 2008

 

We  can achieve this by using router t/r.

chinap24

 Profile Answers by chinap24 

• Apr 11th, 2008

 

using an update strategy transformation...We can insert the new rows using INSERT option in T1 and insert duplicate rows using UPDATE option in T2