I have a scenario in which the source consists of 2 cols
10 A
10 A
20 C
30 D
40 E
20 C
and there are 2 targets one to load distinct values and another one to load duplicate values.
T1
10 A
20 C
30 D
40 E
T2
10 A
20 C
How to load?
Subscribe to:
Post Comments (Atom)
8 comments:
Use an Aggregator Transformation and in the transformation, using count function, try to find out if there are duplicates or not. After aggregator, use a router and route the values based on the count. Hope this helps
first you use an aggregrate and group it by all the columns from source. then add one more column in aggregrator which actually aggregrates the count of each group.Then after that either using filter or router according to ur need send the duplicate records where count is greater than 1.
Then in next step make that table as look-up and send all the duplicate records to final table.
Use an expression transformation to store the previous record and the use variables to compare the records. Use flags to apply the logic and then use router to route the output to two tables.
No need for an aggregator transformation!!!
Hello tanya,
I donot remember the exact process ,but the below thing i have done once for my project...
take a expression transformation create two variable ports in one variable port give the column name for which u need to get the distinct and duplicate values..suppose say column name is A and name the variable as var_A, and in second variable give var_A = A ,1,0...pass this value to the router and give two filter conditions one for routing duplicte values and one for distinct values....and connect to two targets accordingly this will surely work..give it a try and let me know.
thx,
Gaur.Deepti
If the source is a relational then u can write query in SQ..take two sources in one sQ write the query for DISTINCT..and in the other write a querry for DUPLICATE vales..This will work fine i think so...
Hi Guys i exactly agree with amar & deepti wt they said ........its also easy method but according to deepthi method take two variable ports then map those two variable ports for diffrent targets like T1& T2 then u will get distinct records @T1& Dupli Records @T2
i mean with in expression we can devide the Records from two different variable ports no need to go for router from exp tranformation directly connect to two differene targets
SRC - SQ_SRC - SRT - EXP - RTR - TGT1
- TGT2
Sorter:
Sort the data based upon port for which you are finding duplicate.
Expression
Create three ports.
V_COMPARE (Variable Port) = IIF(EMPNO=V_OLD_REC, 1, 0)
V_OLD_REC (Variable Port ) = EMPNO
O_RECORD (Output Port ) = V_COMPARE
Router
Create two Groups
New_Record: O_RECORD = 0
Dup_Record: O_RECORD = 1
Post a Comment