I have a source S and Hash file H as lookup. Source S is looked up against the Hash based on 5 fields. I need to get the matched records based on the 5 fields from the source. But the data for 2 fields may be null. Will I get wrong result because of null data?
Since null != null you must cleanse the data and replace the nulls with something else. Note that the lookup derivation box can be edited (the place where you drop the main link in order to create the lookup) you can write a if-then-else statment meaning
if isNull(DSLinkmain.col1) then 'xx' else DSLinkmain.col1
A simple NullToZero() or NullToValue() function will serve your need. As Amos said, you may not get the output what you need on null comparison. So convert both the nulls to some other value before actual lookup takes place. Here care should be taken that you should not convert the null into a value which the field might can take. For instance, convert the null value to some negative value, if the field can have the value zero.
take that lookup column , put it in a If condition and check for Null , if it is null give some Junk value (it should not match with records) , if it is not null it writes the original values .
Use the same logic in creating the hash file and while using it for the lookup. When creating the hash file you check for null values and for null values give some junk values which you believe can never exist in the source. And While doing the lookup to the hash file use the same junk value to substitue if null found in the source. Doing this will work.
The problem by doing this is
Col1, Col2, Col3, Col4, Col5, Val1, Val2, Val3
1,2,3,4,5,abc,def,ghi
1,2,3,null,5,klm,nop,qrs
1,2,3,null,5,ghi,jkl,mno
In the above example while creating the hash file the 2 record is overwritten by 3rd record due to same key becos of null value in the 4th key column. There the value for the second record is lost. If you belive that this doesn't happen with the record then the first mentioned logic should work without any problem.
Hi,
If you have a row that a lookup key value was not found then anything you mihgt have taken from that lookup is a null value.
File1 has c1,c2,c3 (columns)
values:
1,2,test
2,2,test
File2 (hash lookup) has c1,c2,c3
values:
1,2,Testing lookup
3,2,Testing for lookup continued
Output is File3 with File1.c1,File1.c2,File1.c3,File2.c3
lookup is on c1 and c2.
now a simple lookup will produce 2 rows:
1,2,test,Testing lookup
2,2,test,
where the missing value is @NULL
now you just need to decide what to do about it?, can you put a default value? or perhaps put that row into rejects file (using lookup.NOTFOUND as constraint) to be handled seperetly.
its all really dependant on your bussines rules.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
