If you need to use a custom condition on the condition field in a Conditional lookup, you might have it look like this.
It should not be run unless Inventory Posting Group is not empty.
I got an error
The error message.
Ambiguous column name 'Inventory Posting Group'. Details: SQL Server: 'TX-TL' SQL Procedure: 'usp_Item_Clean' SQL Line Number: 250 SQL Error Number: 209 Ambiguous column name 'Inventory Posting Group'.
The script part that failed.
-- Copy data for the conditional lookup field 'Inventory Posting Group Description' (Algorithm: One lookup, Group by) ; WITH CTE AS ( SELECT MAX(S.[Description]) AS [~Value] , S.[Super Inventory Key] FROM [DSA].[Inventory Posting Group] S GROUP BY S.[Super Inventory Key] ) UPDATE R SET R.[Inventory Posting Group Description] = CTE.[~Value] FROM [DSA].[Item_R] R INNER JOIN [DSA].[Item_T] T ON T.[DW_Id] = R.[DW_Id] INNER JOIN CTE ON CTE.[Super Inventory Key] = T.[Super Inventory Key] WHERE [Inventory Posting Group] IS NOT NULL
Why is does this give an error
You will notice the message is about ambiguous columns and this means that there are two versions of the same field. You can see that in the failing script, at the where statement at the bottom. You will need to prefix it with either T for the transformation view or R for the raw table. Either table will work, but you have to choose the one that best fits the condition.