how to retrive unmatching records from lookup

[suma]

Hi all,
I have a problem with lookup.
In the source say i have 5 records,But in the Look up i have only 3 matching records.When i make a loop up i get the records that are matching. To get the records that are not matching i give the condition in the constrains IsNull(DSLink.1) where DSLink1 is the lookup link.
But everytime i do i get only 0.No records are loaded for the unmatching records.

Help me ut to solve this problem

[anitha_casturi]

Ideally IsNull(DSLink.1) should work.
Why not try ISNULL(match column) and check????

[MaheshKumar Sugunaraj]

Hi,

You could try the following:

hashfile_lookup.NOTFOUND in the constraint.

Should work.

Thanks & Regards
Mahesh

[Mike]

If I've understood your problem: you want to obtain the records in your lookup file that do not exist in your stream input file. You will have to rethink your design strategy. A reference lookup is essentially a left outer join between your stream input and your reference input. It sounds like you're looking for a full outer join solution.

Mike

[ray.wurlod]

Re-read what you asked, which Mike has highlighted. The purpose of a lookup is to find what's there. If it's not there you can't find it! Therefore there will be no "unmatching records".

As Mike said, it appears that you're seeking to perform a full outer join (or union); all values irrespective of source. If both sources are in the same database the easiest way to do this is with SQL (either a full outer join query or a union query).

[changming]

look up can solve this problem, i think,
a lookup will find what is match, if not find, return null. so you capture the null value, instead of not null value.

[ray.wurlod]

Did you build your constraint expression using the expression editor or by manual typing?

The IsNull() function must apply to the key column on the reference input link (the lookup) - for example IsNull(DSLink1.keycolumn)

Your original request suggested that you may not have this totally correctly specified.

[mandyli]

Hi

As ray said for retrieving unmatched records use Join stage(left outer Join). Lookup stage used for chekcing any match value is there in table or file.

please try use join stage.


Thanks
Man

[chulett]

please try use join stage.

Doesn't really help telling someone to use a PX stage for a Server job.

I think we're at the point in this thread where we need to wait for the OP to come back and comment on all the wonderful advice here. Otherwise, we're just talking to ourselves.