Key Expression in transformer for Look Up

[swades]

I have 3 keys (X,Y,Z) in Source and for Look Up I have 2 table (Table A & Table B).

For Table A there are 2 keys (X,Y) which I have to compare with that of Source. conditon for look-up this table is IF (Source.X=TableA.X) AND ((Source.Y=TableA.Y) OR (Source.Z=TableA.Y)) then get data from TableA.

For Table B there are 2 key (X,Y) which is simple i.e.:
IF (Source.X=TableB.X) AND ((Source.Y=TableB.Y) then get data from TableB.

Code: Select all

           

              TableA  TableB
               (X,Y)    (X,Y)
                |       |
                |       | 
                |       |
                V       V
Source------->Transformer-------->Target
(X,Y,Z)

I can put these conditions as constrains on appropriate link using @TRUE & @FALSE System variable

My question here is :1.What will be in Key expression in Left pan of Transformer for TableA.?
2.How key expression works ?

Thanks in advance.

[ArndW]

The "OR" condition is not supported in the key expression. You will need to do both lookups and then do the OR logic in the derivations of the output. Unfortunately conditional lookups don't exist in server jobs.
If you are doing lookups onto a relational source you could use user-defined SQL to do the OR condition for you. But if you are reading hashed files you cannot use that alternative.

[chulett]

Just to clarify, of course you can use an 'OR' in a Key Expression to conditionally set the value being used in the Key for the lookup. For your particular problem, you need to conditionally check the results of the two lookups, not futz with their key expressions.

[swades]

Thanks Craig,
If I use (Source.Y=TableA.Y) OR (Source.Z=TableA.Y) in Key Expression for Column Y of TableA in Left pan AND again put the condition as constrain on appropriate Output Link then will it be fine ?

Can you please advise me ?

Thanks

[chulett]

No, you can't put that in the Key expression. That evaluates to TRUE or FALSE and what needs to go there are the key values to be used by the lookup. Something like that should be your constraint or output derivation.

[swades]

Yes craig,I checked that if I used ''OR'' in Key Expression and condition on link Constraints then it is not working.

So , In general
1. If I mapped only 1 coumn in Key Expression and Use 2 columns on Link Constrains, then will it work ?
2.Can anybody please explain working and uses of Key Expression of transformer left pan?

Thanks in advance for your time.

[ArndW]

swades - you haven't stated if you are retrieving data from a hashed file or a database in your lookup. As mentioned before, if it is a database then you can put your OR expression into the SQL lookup. If you are referencing a hashed file then you need to do 2 lookups and then perform the OR logic in the transform. Or you might use ODBC or universe lookup to allow that relational OR logic (at the price of performance).

The "Key Expression" derivation is a link or logic that evaluates to a single value that is used to lookup that key, i.e. one side of an "=" expression.

[swades]

Sorry for being late, Arndw,
I am using my look up as Hashed_File_A and Hashed_File_B so can you please more explain about how I can implement logic which you said ?

Thanks in advance.