Default value for Continue option in Lookup

wahi80 · Post by **wahi80** » Wed Jun 04, 2008 1:24 pm

Hi,

Im doing a lookup with a sequential file as source and a reference dataset.
The requirement is that if there are 100 rows in source there should be 100 in output after the lookup. Hence I used the Continue option in case of lokkup failure.But another need is that all numeric fields should be populated with value -99 if lookup failed and these fields were not populated. Currently the value is 0,hence I'm unable to midentify if the 0 is due to lookup failure or it was already a value in the field.

Regards
Wah

ray.wurlod · Post by **ray.wurlod** » Wed Jun 04, 2008 3:30 pm

You can not do this in the Lookup stage itself (one stage, one task) but can do so in any downstream Modify or Transformer stage. Pass the columns from the reference link to the output and replace null with -99 as required.

wahi80 · Post by **wahi80** » Wed Jun 04, 2008 3:35 pm

Hi Ray,

But the problem is that the lookup is populating 0 for these numeric fields.
Im unable to distinguish if the 0 is beacuse the lookup matched or because there was no match.

Regards
Wah

ray.wurlod · Post by **ray.wurlod** » Wed Jun 04, 2008 3:37 pm

Make the output fields nullable.

wahi80 · Post by **wahi80** » Wed Jun 04, 2008 7:53 pm

I made the fields nullable, but for integer fields a value of 0 is still populate but for varchar fields null is populated

ray.wurlod · Post by **ray.wurlod** » Wed Jun 04, 2008 10:32 pm

That's fine, use one of the VarChar fields to test whether the lookup was successful or not. Ideally use a field that is not null in the reference source, so that you can be confident that, if it is null on the output, this is because the lookup failed.

wahi80 · Post by **wahi80** » Thu Jun 05, 2008 8:25 am

My job looks like this:

Code: Select all

                    
                        RefCharLkp
		                   |
		                   |
		                   |
		                   |
SourceSeqFile----------Lookup---------OuSeqFile
		                   |
		                   |
		                   |
		                   |
		                   |
	                      RefNumLkp

The problem is any rows not matching with RefCharLkp, the value of null is populated for the column, but for rows not matching RefNumLkp 0 is populated. Hence Im unable to make out the difference.

Regards
Wah

Meera · Post by **Meera** » Thu Jun 05, 2008 8:55 am

Try using Reject option in Lookup stage instead of continue. For the records which are coming from the Reject link, apply the necessary derivation by using a transformer or any other suitable stage. Then combine the results from both the links finally.

wahi80 · Post by **wahi80** » Thu Jun 05, 2008 9:26 am

I will have to reject records for the RefNumLkp only and use Continue for RefCharLkp, and then combine both.
This is just a sample job I put, the actual job has many numeric lookups in different Ref files. I guess I will either have to break up the jobs or use successive stages of lookups

manuel.gomez · Post by **manuel.gomez** » Wed Jul 23, 2008 3:47 am

I am really surprised how some tasks that were so easy in Server, becomes so difficult and results in tons of stages in your canvas in Parallel
This, in server could have been accomplished with a simple Transformer receving as many hash files as need

I guess this is forced to get benefits of partition processing, but gets designing much harder....