June 27, 2011 at 7:38 pm
I am using the Transformation editor in BIDS 2005 to change a column Division buyer when column division code is equal to different values.
When Column Division code = Null, Column: Division buyer = Starcom - Non Tech
When Column Division code = MV, Column: Divsion buyer = Mediavest
When Column Division code = SP, Column: Division buyer = Starcom - P&G
I have written this but it doesn't work, can anyone help?
Division Buyer, Replace Division Buyer,
([DivisionCode]==MV?[Mediavest]: ([DivisionCode]==SC?[Starcom - Non Tech]:[DivisionCode]==SP?[Starcom - P&G]:[DivisionCode]==ST?[Starcom - Tech]:[Starcom - Non Tech]))
This much simpler code also doesn't work,
Division Buyer, Replace Division Buyer, ISNULL(DivisionCode)?"Starcom - Non Tech" : DivisionCode
June 27, 2011 at 11:25 pm
I worked it out, the problem was the string were delceared as variables.
This works,
([DivisionCode]=="MV"?"Mediavest":([DivisionCode]=="SC"?"Starcom - Non Tech":[DivisionCode]=="SP"?"Starcom - P&G":[DivisionCode]=="ST"?"Starcom - Tech":"Starcom - Non Tech"))
June 28, 2011 at 2:21 am
Although you may have solved this problem in the short term, you may be storing up problems for yourself in the medium term. If two or three more code/description pairs get added, your nested-if expression turns into an unreadable cypher.
To avoid this and get a performance boost at the same time, consider creating a physical table which contains all the mappings
Code Description
MV Mediavest
etc etc
Then, use a query for the selection your source data which joins to this new table. Then you have all your codes expanded easily, regardless of how many there are or how frequently they are updated.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply