Hello, I found yesterday a similar issue and (probably) found a solution.
I initially used the same technique explained by Shong (which is btw the same described shortly in the User Guide).
It works, but I had more than a lookup table and I wanted to add a flag telling me which (one or more) lookup failed the inner join causing the reject.
I did not manage to calculate it using the inner join, so this is how I did it :
I have row1 being the main flow, row2 and row3 are lookups where the lookup key is CustomerID (String) for row2 and EmployeeID for row3
1) Both two lookups are set to LEFT OUTER JOIN
2) I calculate two boolean variables customer_reject and employee_reject
3) I add an output flow called reject, but it is a STANDARD output, I just add the filter (Var.customer_reject || Var.employe_reject), it cointains the two flags in output
4) Obviously in the main output flow the filter is !(Var.customer_reject || Var.employe_reject)
Now, the issue I had was in calculating the flags as variables :
1) The first one is a String and when no match is found it's value is null (which is quite "normal" in left outer joins)
2) The other one was a integer and while I was expecting nulls as well, I found zeros (!!!!) 0. for this one I used a different calculation (see screenshot)
@Shong, is there a way to generate nulls instead for case 2)?
Zero can be a valid lookup code, therefore it might be a bit confusing, is it coming from some default? I looked around but found none.
Also, if I use inner joins with multiple lookups, will TOS try to match ALL the lookups if the record is unmatched with the first one?
Maybe, as a feature would be cool to have an additional variable or field automatically calculated for each lookup in inner join which gives you the rejection flag.
Thanks,
Francesco
