Use Lookup Rows' Own Column as Variable in tMap

[Anonymous]

I'd like to have a conditional statement in the join expression key where it checks the value of ref_type, and pick the appropriate column  from out3 as key to match with ref_code (if ref_type=='record type', then ref_code = record_type, termination_cause if == 'termination_cause'

 Similarly in query it is something like:

select a.calling_number, b.ref_code, b.value from stg.cdr_smsc_tango a, SOR.REF_CDR_SMSC b
where case
when b.ref_type == 'RECORD_TYPE' then a.record_type=b.ref_code
when b.ref_type == 'TERMINATION_CAUSE' then a.TERMINATION_CAUSE=b.ref_code
when b.ref_type == 'MSG_STATE' then a.MSG_STATE=b.ref_code
when b.ref_type == 'MSG_STATUS' then a.MSG_STATUS=b.ref_code
END

Is it possible to do so in Talend?

[fdenis]

we did not get your message!

[Anonymous]

Sorry there was an error with my internet connection, I've edited the message. Thanks

[Anonymous]

As mentioned in the screenshot , you can put the or condition for the join and then put the condition as row7.id_lookup == row6.id?id : id2 for the output column on the output side of the tmap, Add nested if statement for more column values as id3,4 and so on.

Thanks ,

Praveen.

Please mark it as solved if applicable and kudos as bonus !

[Anonymous]

I think you get my message wrong, in my case I want to check the value of ref_type and put the join statement with ref_code, not ref_type

[fdenis · Accepted solution]

you can remove all link between input tables (set retrieve all matches) and add your condition on the output.
it's sometime more easyer.

[Anonymous]

Do I choose this as the match model then?

[fdenis]

so you are now into a full join (try it). then you just have to add your filter on output side.

[Anonymous]

Okay I understand. Thanks!

[fdenis]

tag solved if done.
good luck