Lookup issue with blank column

siauchun84 · Post by **siauchun84** » Tue May 24, 2011 12:38 pm

Hi

I have a job with a Lookup stage which will lookup for the location ID from a reference file.

When my input is "" (blank), it cannot retrieved the location ID which assigned for the value of the "" (blank).

Anyone face this before and any solution for it?

Please advice.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Tue May 24, 2011 5:05 pm

Please be clear here.

Are we talking about a blank (" ") or a zero-length string ("") as the lookup key value? Your terminology is loose.

singhald · Post by **singhald** » Wed May 25, 2011 12:20 am

As Ray suggested , please be clear when you post your problem.

I believe you can assign a dummy value(like 'X' should be unique and different from your location id available in reference link) to input key in case of null.

do the same with your reference link data )assign 'X' to location id which is null.

now lookup stage will fetch location details for 'X' locaiton id which it is basically null in source .

siauchun84 · Post by **siauchun84** » Wed May 25, 2011 8:57 am

Hi Ray

Sorry for the confusion, it was zero-length string ("").
In the reference file, we have assigned a location ID for the "" value.
Whenever the input records have "" value, it will get that assigned location ID for it.
This terminology was working fine when in Server job previously.
I just hit this issue when converting the Server jobs into the Parallel Jobs and those records with the zero-length string rejected during the lookup stage.

Was this the limitation for the lookup stage or I have to turn on some setting for it?

Hi Deepak

Thanks for your great idea given. Actually I have tried your idea but it bring into the jobs performance issue as it will running slow because I have to add additional stage to perform the assign dummy value task for both input link and reference link.