How to Find Matching and Unmatching records from two files?

Formally known as "Mercator Inside Integrator 6.7", DataStage TX enables high-volume, complex transactions without the need for additional coding.

Moderators: chulett, rschirm

Post Reply
Balkrishna
Participant
Posts: 4
Joined: Wed Sep 20, 2006 12:07 am

How to Find Matching and Unmatching records from two files?

Post by Balkrishna »

I am having two input files as -

input 1 input 2
A A
B B
C C
F D
E

I want output as :
A with data from Input 1 and Input 2
B with data from Input 1 and Input 2
C with data from Input 1 and Input 2
D
E
F with data from Input 1

Or if is not possible then create two outputs

Output 1 - Matched records

with
A with data from Input 1 and Input 2
B with data from Input 1 and Input 2
C with data from Input 1 and Input 2

and output 2 - Unmatched Records
with
D
E
F
Bala
ag_ram
Premium Member
Premium Member
Posts: 524
Joined: Wed Feb 28, 2007 3:51 am

Re: How to Find Matching and Unmatching records from two fil

Post by ag_ram »

Balkrishna wrote:I am having two input files as -

input 1 input 2
A A
B B
C C
F D
E

I want output as :
A with data from Input 1 and Input 2
B with data from Input 1 and Input 2
C with data from Input 1 and Input 2
D
E
F with data from Input 1

Or if is not possible then create two outputs

Output 1 - Matched records

with
A with data from Input 1 and Input 2
B with data from Input 1 and Input 2
C with data from Input 1 and Input 2

and output 2 - Unmatched Records
with
D
E
F
Use a Change Capture and Change Apply Stage in conjunction
Balkrishna
Participant
Posts: 4
Joined: Wed Sep 20, 2006 12:07 am

Re: How to Find Matching and Unmatching records from two fil

Post by Balkrishna »

Hi Ram,

Can you please explore the same.
It will be a great help to me.

Thanks,
Balkrishna
Balkrishna wrote:I am having two input files as -

input 1 input 2
A A
B B
C C
F D
E

I want output as :
A with data from Input 1 and Input 2
B with data from Input 1 and Input 2
C with data from Input 1 and Input 2
D
E
F with data from Input 1

Or if is not possible then create two outputs

Output 1 - Matched records

with
A with data from Input 1 and Input 2
B with data from Input 1 and Input 2
C with data from Input 1 and Input 2

and output 2 - Unmatched Records
with
D
E
F
Bala
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Re: How to Find Matching and Unmatching records from two fil

Post by DSguru2B »

ag_ram wrote: Use a Change Capture and Change Apply Stage in conjunction
Does DataStage TX comprise of those stages ? :?
I am not sure how you will do this with TX but since your on unix, you can easily do this with a simple 4-5 liner shell script.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
jvmerc
Participant
Posts: 94
Joined: Tue Dec 02, 2003 12:57 pm

Post by jvmerc »

I suppose you'd have to own Change Capture and Change Apply Stage first. Then you could figure out if you can execute their code from the TX map.

Many ways to do this....

How about merging the files then use the unique function.

You could also merge the files, perform a sort and compare current record to last record read.

Load all the data to a DB then use select unique/distinct.
.
.
.


Problem with trying to work with two distinct files is that you'd probably need to use the LOOKUP or SEARCHUP/DOWN functions. If your files are small it may not be a big deal but the larger the file the more resources and time. Also, each of those functions has some limitations.

Depending on the data, if I used any of the above solutions, I'd probably define each record as a text blob until I had the data I wanted.
Post Reply