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

Use Lookup Rows' Own Column as Variable in tMap

Anonymous — Sat, 16 Nov 2024 07:32:49 GMT

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?

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

fdenis — Tue, 02 Oct 2018 10:06:55 GMT

we did not get your message!

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

Anonymous — Wed, 03 Oct 2018 08:53:35 GMT

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

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

Anonymous — Wed, 03 Oct 2018 09:21:00 GMT

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 !

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

Anonymous — Wed, 03 Oct 2018 09:43:31 GMT

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

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

fdenis — Wed, 03 Oct 2018 09:48:09 GMT

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

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

Anonymous — Wed, 03 Oct 2018 09:57:11 GMT

Do I choose this as the match model then?

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

fdenis — Wed, 03 Oct 2018 09:59:55 GMT

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

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

Anonymous — Wed, 03 Oct 2018 10:01:09 GMT

Okay I understand. Thanks!

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

fdenis — Wed, 03 Oct 2018 10:02:59 GMT

tag solved if done.
good luck