Hi,
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?
-
Null data compare
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 385
- Joined: Tue Oct 07, 2003 4:55 am
Hi,
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
HTH,
Amos
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
HTH,
Amos
Hi Badri
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.
Regards,
Vignesh.
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.
Regards,
Vignesh.
-
- Participant
- Posts: 38
- Joined: Sat Feb 28, 2004 12:11 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Thanks
Siva
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.
Thanks
Siva
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,
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.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org