Use Lookup Rows' Own Column as Variable in tMap

Four Stars

Use Lookup Rows' Own Column as Variable in tMap

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'Capture.PNG

 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?


Accepted Solutions
Twelve Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!


All Replies
Twelve Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

we did not get your message!

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Four Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

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

Re: Use Lookup Rows' Own Column as Variable in tMap

Capture.PNG

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 !

Four Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

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
Twelve Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Four Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

Do I choose this as the match model then?Capture.PNG

 

Twelve Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Four Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

Okay I understand. Thanks!
Twelve Stars

Re: Use Lookup Rows' Own Column as Variable in tMap

tag solved if done.
good luck

Francois Denis

Tag as "solved" for others! Kudos to thanks!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now