I have a table like this:
Customer | Group |
A | 10 |
B | 20 |
I need to do a translation of "group" to “local group”:
Customer | Group | Local Group |
A | 10 | 11 |
B | 20 | 21 |
When a match is found, the group code should be replaced by the local group code ... but, when no match is found the group code should stay.
Is there any way to do this using Lookup ? I tried but when you set the error output to ignore it replaces the value that has no match with a NULL value. Maybe there is another way to get this done with or without the lookup component ?
The lookup transform does not 'replace' columns; it adds aditional columns to the data flow; and yes, when a match is not found and the error output is set to ignore error; it will put NULL in the new column(s). What I would do is to use a derived column after the lookup tarnsform. The Derive Column would have a column as "replace group" with an expression like:
ISNULL(LocalGroup) ? Group : LocalGroup
This expression is trying to replace the value of group only when Local group is not null
I hope this helps you
Rafael Salas
|||you could use the lookup transformation with a sql command to provide the result set.
select
c.Customer
, c.Group
, isnull(g.localgroup, c.group)
from yourcustomertable
left outer join yourgrouptable g
on c.customer = g.customer
is psuedocode that would do what you need (assuming this is how your tables are joined)
it will select the customer and group from the customer table, and the left outer join will force NULL for localgroup where you dont get any data returned from the join.
wrapping ISNULL around the localgroup will replace those nulls (due to not having a localgroup) with the customergroup|||Thanks for your answers, I'll try them out and see what works best for me.
No comments:
Post a Comment