Hi All,
I am working on a table fields in an object in DW. The table contains data that are dynamic in a way, where one field is related on the other. Let say that this table contain field for a Staff ID and Manager ID for void transactions, whereas i need to show the manager ID if there is data in the column while if there is none, then the Staff ID data will be shown.
Any idea on how i can write this condition on SQL custom field transformation?
TIA
I would do the following:
1. Add a custom field
2. Add 1 Field Transformation to the custom field. This Field Transformation will return the value of the ManagerID field
3. Right click on the Transformation that was added in the project tree and add a condition to the Tranformation. Specify that the column you need to check IS NOT EMPTY.
4. Add a second Field Transformation to the custom field. The transformation will return the StaffID value
5. Add a condition to the 2nd field transformation, indicating the field you are checking for a value IS EMPTY.
This is the easiest way to accomplish your goal. Another option would be adding a single custom field transformation using SQL Case logic.
Hope that helps!
David Zebrowitz
I didn't see your question about writing a custom field transform. It would look something like this:
CASE isnull(ltrim(rtrim([mycolumn])),'')
WHEN '' THEN [Staff ID]
ELSE [Manager ID]
END
Hi David,
Appreciate your support on my concern but i chose the option using the SQL script transformation and gives the condition i need.
Thanks anyway. :)