We have searched, and been unable to find a thread referencing this.
We have two links out of transforms, going to a lookup. We specify a key column, and if that key matches in the lookup, we want to drop the record, and only send through rows that do not match. We have tried changing the parameters on the lookup, to no success. We ended up putting a transform after the lookup in order to drop those records that were not set to null (or space) in the lookup. Is there any way to do this in just the lookup stage?
Drop when lookup key matches?
Moderators: chulett, rschirm, roy
You can have a reject link on the lookup. Just set your lookup to Fail on reject. Any input columns which fail will be output on the reject link. Think of the reject link as lookup value not found rather that a reject. From the reject link you can process your records.
Output the rows which are found in the lookup to a copy stage with no further outputs.
Output the rows which are found in the lookup to a copy stage with no further outputs.
Thank you for your advice. We did that, and it worked - however, as we are not able to map the output for the link and the table we are referencing has different column names than the input from reject, we had to manually edit the SQL. I'd prefer not to have to do that for each job we create. Are there any other options to accomplish what we're trying to do, outside of your suggestion or the workaround we had done before? Thanks.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use a Copy stage or a Transformer stage (or, indeed, pretty much any active stage type) to effect the column name changes during mapping from input to output.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Drop when lookup key matches?
You can use a join stage (full outer) and make the condition in TFM and then do whatever you want!
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.