DSXchange

Hi,

Regarding the Lookup or Join Stage -

Consider the scenario where records in driver file DO NOT find a match (reject) based on the Key in the lookup/ reference file.
Then the fields which the lookup/ Join was supposed to pick up from the lookup file / reference file would be populated as blank or null (?) in the corresponding lookup/ join link.

Can each of these stages behave differently across different runs (for same job) - meaning for no match - can it have blank at one time ("") and NULL at other times in the fields being picked up from lookup file.
If so, how can this be resolved?

Essentially, my aim is if my matched and unmatched records in lookup or join stage are allowed to go in the same link how can I logically differentiate them?

Thanks,
Nitin

Hi Nitin,

Is NULL or blank a valid value in the lookup file columns which are being outputted?

yes - am making sure that the output fields are allowed to take NULL values so that passing NULL values should not be a problem.

Well I think u got my question wrong nitin. What I wanted to ask was is NULL or blank a valid value in the file which u are using as a reference file for the lookup? If not, then you can easily differentiate in the output records the records for which the lookup was not successful, by looking at these column's value.

Hope I am clear enough this time round

If it works anything like a Server job lookup, then you have two options:

Don't check the data fields in the lookup, check one of the key fields for NULL. If you put a value in the key field and it comes back NULL, then you know the lookup failed.

Is there a 'status' you can check? In Server jobs you can check LookupLink.NOTFOUND for True/False to indicate Failure/Success of the lookup.

my aim is if my matched and unmatched records in lookup or join stage are allowed to go in the same link how can I logically differentiate them?


What is the meaning of this? How you can pass the the matched and un matched through the same link.

One thing you can try is do the sourting before the join stage. You can set it as ascending or decending.

Try it.

Hi
When you have a join or lookup with only one output link ( matched and unmatched records come in same link) you can identify these records by fetching a not null field from the reference link. If you have any such column, then it should be made nullable yes on the output link.

Then on the output side you will get a null in this field. If this field is not null on the output side also then you will get "" for varchar fields for non matching records and 0 for numeric fields non matching.

It is better if you make it nullable yes so that you can differentiate these default values and nulls and handle the records properly.

Regards

Now i understood your problem. IF you are using the any of the join or Look up stage if it is matched you will get the output cloumns properly else that field should should contain null values. You can even handle this. use the null handle property and one another option is also there, You can set a default value for this. Like that you can handle the null values. Try it out with different data types. What ever it may be you will get atleast quotes.