The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.
The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?
Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.
Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:
"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."
|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.
There are too many ifs and caveats to make this a good solution, IMHO.
Thanks,
No comments:
Post a Comment