I have 2 files 1st contains duplicate records only, 2nd file contains Unique records.EX:
File1:
1 subhash 10000
1 subhash 10000
2 raju 20000
2 raju 20000
3 chandra 30000
3 chandra 30000
File2:
1 subhash 10000
5 pawan 15000
7 reddy 25000
3 chandra 30000
Output file:-- capture all the duplicates in both file with count.
1 subhash 10000 3
1 subhash 10000 3
1 subhash 10000 3
2 raju 20000 2
2 raju 20000 2
3 chandra 30000 3
3 chandra 30000 3
3 chandra 30000 3
Answers were Sorted based on User's Feedback
Answer / subbuchamala
File1,File2====Funnel-----Copy=======1st link AGG, 2nd link JOIN----Filter----OutputFile
1. pass the 2 files to funnel stage and then copy stage.
2. from copy stage 1st link to AGG stage, 2nd link to JOIN stage
3. In AGG stage, Group by Key column say ID, NAME take the count and JOIN based on KEY column
4. Filter on COUNT>1 send the output OutputFile
we get desired output
Is This Answer Correct ? | 14 Yes | 0 No |
Answer / ankit gosain
Hi,
This problem can be solved by creating a job with following
stages:
File2 File2
| |
| |
| |
File1-----Funnel----Aggregator----Join----Filter---Tgt_File
|
|
|
File1
1. Funnel both the files (Now you have Unique & Duplicates
records).
2. Aggregate on the basis of any i/p column and mention the
calculation type = Count Rows (say o/p column row_count).
3. Join the aggregated o/p with the i/p file1,2 one the
basis of key & mention the join type = Inner Join.
4. In filter stage, mention the where clause as row_count>1.
If you have further doubt or query, catch me on
ankitgosian@gmail.com
Cheers,
Ankit :)
Is This Answer Correct ? | 1 Yes | 0 No |
EXPLAIN SCD
How you can fix the truncated data error in datastage?
in aggregator , how can i get the sum in readable format
specify data stage strength?
What are transforms and what is the differenece between routines and transforms?
What is the function in Transformer stage can act as Rank Function in oracle
how does work server jobs?
What is the project in datastage?
Triggers,VIEW,Procedures
i have source like deptno,sal 1,2000 2,3000 3,4000 1,2300 4,5000 5,1100 i want target like target1 deptno,sal 1,2000 3,4000 4,5000 target2 2,3000 1,2300 5,1100 with out using transformerstage
What is the difference between SQl Loader and OCI in datastage?
CHANGE CAPTURE