Hi ,
Does anybody tell me to how to do compare the source and target data and reject the records into a file which does not fond a match and also the count of the number or records matched and the count of the records that are not matched.
Thanks
Data Comparision
Moderators: chulett, rschirm, roy
Hi,
You can have two inputs to transformer.
1. Source table
2. Target table (as a lookup)
Match the keys in two tables
Use Isnull fucntion on this match
have two outputs from transformer
1. Match records
2. Non match records
Depending upon output of Isnulll fucntion direct to match or not match file.
use @outrownum fucntion to find records written to each link.
Ketfos
You can have two inputs to transformer.
1. Source table
2. Target table (as a lookup)
Match the keys in two tables
Use Isnull fucntion on this match
have two outputs from transformer
1. Match records
2. Non match records
Depending upon output of Isnulll fucntion direct to match or not match file.
use @outrownum fucntion to find records written to each link.
Ketfos
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How is the match to be performed? For the sake of example, let's assume only the key is to be matched.
Optionally pre-load the target's keys into a hashed file. This is for performance, not functionality.
Construct a job that reads the source, performs a lookup against the target, and writes to either of two outputs ("matched" and "unmatched").
To determine which output link to use, constraint expressions are used to determine whether the lookup succeeded - you can use the input link variable NOTFOUND, or an IsNull on the reference input key column.
DataStage itself gives you the count; you can include @OUTROWNUM as a column on each link (the final row contains the count), you can get the counts from the job log, you can write routines that determine the link row counts.
Optionally pre-load the target's keys into a hashed file. This is for performance, not functionality.
Construct a job that reads the source, performs a lookup against the target, and writes to either of two outputs ("matched" and "unmatched").
To determine which output link to use, constraint expressions are used to determine whether the lookup succeeded - you can use the input link variable NOTFOUND, or an IsNull on the reference input key column.
DataStage itself gives you the count; you can include @OUTROWNUM as a column on each link (the final row contains the count), you can get the counts from the job log, you can write routines that determine the link row counts.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
Sainath.Srinivasan
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
