- UNIFYBroker Forum
-
Questions
Join Conditional on Not-Null
I've had a look at the Join transformation and I don't think this functionality is there but is it possible to join one attribute if it has a value, otherwise join on another? Could this be achieved with a PowerShell transformation that populates a third attribute that does the non-null populating then join on that third attribute?
The object model that I've got this situation for is an Employee table and a Position table. The Employee table contains two attributes placePosition(contains the position number of the position someone is acting in, otherwise is null) and actPosition(contains the persons 'actual' position number, always has a value). I want to join Employee to Position on placePosition if it has a value, otherwise join to position on actPosition. Is the way to do this with a PowerShell transformation that calculates out the null to a third attribute, CurrentPosition or something, and then join on that.?
Answer
Hey Dan,
You're right - the powershell transformation mixed with a join transformation would be the best way to do this.
You would put the powershell transformation first, and have populate a third attribute called something like calculatedPosition. You'd use the logic as above - put the placePosition if it's populated, otherwise use actPosition.
Then you would add a join transformation, and join to the position connector using the calculatedPosition field. That way you're always going to join on the value that the powershell transformation populates.
Customer support service by UserEcho
Hey Dan,
You're right - the powershell transformation mixed with a join transformation would be the best way to do this.
You would put the powershell transformation first, and have populate a third attribute called something like calculatedPosition. You'd use the logic as above - put the placePosition if it's populated, otherwise use actPosition.
Then you would add a join transformation, and join to the position connector using the calculatedPosition field. That way you're always going to join on the value that the powershell transformation populates.