topic Re: Multiple conditions on tMap in Talend Studio

Multiple conditions on tMap

FGuijarro — Fri, 15 Nov 2024 23:40:30 GMT

Hi,

I am developing a process from 4 input tables (A, B, C, D).

-Relation between A and B is a1 (a1 is in table A and B)

-Relation between B and C is b1 (b1 is in table B and C)

-Relation between C and D is c1 (c1 is on table C and D)

Conditions are in columns on table B and D.

Output contains columns from A, B, C

How can I develope process the process with the tMap?

If I choose a main table (A), then B, C, D are lookup processes?

Which are the optionsfor this process?

Thank you for the help.

Re: Multiple conditions on tMap

gjeremy1617088143 — Thu, 30 Sep 2021 08:16:18 GMT

Hi @Fernando Guijarro , in tMap you can make join between lookup, so you can choose A as main and BCD as lookup, make a join between A and B , then make a join between B and C then C and D,

(for each join tou can choose the type of join),for conditions you can use the expression filter on input or output it depends of what you want.

Send me love and kudos

Re: Multiple conditions on tMap

gjeremy1617088143 — Thu, 30 Sep 2021 08:22:45 GMT

for example I have 3 file :

first one

1;coucou

2;hello

second one

1;3

2;4

third one

3;5

4;6

so i want to join 1 with 2 and 2 with 3

and i want only in the third one the line with value 6:

Re: Multiple conditions on tMap

FGuijarro — Thu, 30 Sep 2021 08:34:50 GMT

Thank you Jeremy!

What about if relation between (row1-row2) and (row1-row3) is the same?

For example:

row1.Column1 == row2.columnB

row1.Column1 == row3.columnC

The query that I am trying to develope withTalend is the next. I`ve also attached a pic of my design.

My problem is how to specify the LEFT JOIN and also the conditions

tf.activo LIKE 'ON'

AND p.prefijo NOT LIKE '00%'

because column "prefijo" is VARCHAR in all tables (row13, row16) EXCEPT in row15 that "prefijo" is BIGINT

SELECT

p.prefijo,

p.destination,

t.precio_compra

FROM

inter.tarifas_finales t

LEFT JOIN inte.t_f tf ON t.t_f = tf.id

LEFT JOIN inter.prefijos p ON t.prefijo = p.id

WHERE

tf.activo LIKE 'ON'

AND p.prefijo NOT LIKE '00%'

ORDER BY p.prefijo ASC;

Re: Multiple conditions on tMap

gjeremy1617088143 — Thu, 30 Sep 2021 08:40:00 GMT

You can also make the join from the main on multiple lookup

Re: Multiple conditions on tMap

FGuijarro — Thu, 30 Sep 2021 08:45:07 GMT

Re: Multiple conditions on tMap

gjeremy1617088143 — Thu, 30 Sep 2021 08:53:05 GMT

so you use row1 as main and row2 row3 as lookup , you made left join between 1 and 2 id , 1 and 3 id

then in the filter expression of the output :

("ON").equals(row2.activo) && row3.prefix != null && StringHandling.LEN(row3.prefix)>1 && !(("00").equals(StringHandling.LEFT(row3.prefix,2)))

then you can use tSortRow component to process the order by

Re: Multiple conditions on tMap

gjeremy1617088143 — Thu, 30 Sep 2021 08:58:55 GMT

use load once instead of reload at each row

Re: Multiple conditions on tMap

gjeremy1617088143 — Thu, 30 Sep 2021 09:00:25 GMT

and you can surely make join between 13 and 16 instead of 15 and 16

Re: Multiple conditions on tMap

FGuijarro — Thu, 30 Sep 2021 09:43:34 GMT

Thank you Jeremy!

I´ve developed this with your instructions.

Now I´m getting this

Design is :

Re: Multiple conditions on tMap

XJ_1630 — Thu, 30 Sep 2021 10:11:48 GMT

for completeness

if you are joining datasets from the same DB:

do the joins in SQL and use the result as your input

Re: Multiple conditions on tMap

FGuijarro — Thu, 30 Sep 2021 16:39:49 GMT

Thank you all, design is already done and working with "select as Best" from gjeremy1617088143