What is the best way for me to proceed if I'm in the following situation: I have a main flow of data that matches two lookups on a inner join and I have two other lookup that I don't want to restrict the main flow but only to outpput if they match (In this sense they are not really lookups but however there would be oneoutput by main flow iteration regardless of these lookups but if there is a match I need their informations). Is a non-inner join the right thing to do?
I have been using the non-inner join lookup and mapping the lookup field to the output and that seems to work well, e.g. if there is a match from my input table to my lookup table, it populates the output with the matching data, otherwise it populates the output with a null value. However, I have hit a snag. I have GetInvoiceCost as my main input. I have LocationsLUFilt as a lookup. These are joined (non-inner) on GetInvoiceCost.location = LocationsLUFilt.location What I want to do is if both the input eqnum and location fields are not null, then populate the output location field with the input eqnum field, otherwise, populate the output location field with the lookup location field (so that if there is a match it is populated with the matching value, otherwise it is populated with null) In my output (InvoiceCostOut) I have the following expression: !(GetInvoiceCost.eqnum == null) && !(GetInvoiceCost.location == null) ? GetInvoiceCost.eqnum : LocationsLUFilt.location When I run this, I'm getting values in my location output that don't exist in the lookup table and I can't figure out why. Shouldn't the non-inner join/filtering for null function work the same regardless of the other activity going on??