Multiple conditions on tMap

[FGuijarro]

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.

[gjeremy1617088143]

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

[gjeremy1617088143]

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:

 

[FGuijarro]

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;

[gjeremy1617088143]

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

[FGuijarro]

[gjeremy1617088143 · Accepted solution]

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

[gjeremy1617088143]

use load once instead of reload at each row

[gjeremy1617088143]

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

[FGuijarro]

Thank you Jeremy!

I´ve developed this with your instructions.

Now I´m getting this

Design is :