Handle not null reference data while using Lookup stage

Most of the time you may have seen the populated referenced data as junk, specially if the required referenced column is of type Date. To be specific junk date could be like “**********” this depends on the format of the date chosen.

Reason behind this could be that the stream link is having NULLs against which the references are being checked. To avoid such junk information we must keep the following in mind while using Lookup:

1) Before using the Lookup stage always think and be sure about the stream and reference. It is not always true that reference would have smaller number of records to refer from. In case of sparse Lookup the reference can have records more than the number of records into stream. Hence always make sure what data is going to flow into stream and reference.

2) Be sure about the metadata of the stream and reference. Make sure you know about the NULLs and from where they are coming. 

3) In case stream link is having NULLs and it needs to be referred against any dataset or table then set the Lookup condition. There are three areas to look for while specifying the condition 

a) Derivation 
b) If Condition Not met 
c) When Lookup fail

Above three setting needs to be applied when stream may have NULL into it. First property allows to use functions, input columns etc. For example address_id is the field which is coming from stream but can have null then in derivation area we should write IsNotNull(address_id). Now in the “Condition Not met” section specify to drop, fail, reject or to continue. If you select continue without specifying the condition then junk could come up into the referenced data

Leave a Reply

Your email address will not be published.