Drop when lookup key matches?

[d-woo]

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?

[ShaneMuir]

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.

[d-woo]

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.

[ray.wurlod]

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.

[SURA]

You can use a join stage (full outer) and make the condition in TFM and then do whatever you want!