DSXchange

Hi All,

My Data is coming from source with state code CA, NJ, MI, NULL, IL, KY..... and lookup OCI is having all 54 state code when i am doing lookup in stage and the cond is If field.StateCD = "NULL" or " " then "XX" else field.StateCD.

but the result records is coming 389, 611 is to rejects.

Is any thing worng in code. please help me.

Thanks

Lookup stage has a property called 'IF not found' which you have to set according to what you want, if you use 'Continue' it will send the record along to its output link with Null or a 0 depending on that column format. I did a similar thing earlier, where lookup was on a number column, so it returned 0 when it didnt find corresponding record in lookup table...

as far as i know..there is no way you can put that logic in the Lookup stage

what you can do in a case such as yours is, have a transformer either before or after lookup to put that logic in the derivation for that column. I would use a transformer after the lookup stage, to replace null as whatever default value you wish to have. Coz i have successfully implemented that in my case..when it returned a 0, i replaced it with some 999999 value.

I'd followed jreddy's advice and have a transform after the lookup.

Also, just to make sure since this is how you typed it into the post,
If field.StateCD = "NULL" will look for a string constisting of N,U,L,L. Is the source data has a true null as opposed to the string word "NULL" then us the IsNull() function to test.