tmap, lookup, insert new rows

[Anonymous]

Hello again!
i´ve got the following two tables like:
table1)
Coinfind:
id Issuer Name
360 3 Aedui
366 34 Augustus
128 4 Aedui
... ... ...

table2)
Issuer:
id Name
1 Augustus
2 Tiberius
4 Julius
5 Aedui
.. ...
33 Augustus

table1 mysqlinput --------> tmap --------------> tlogrow (should be: result table, mysqloutput)
^
|
|
table2 (lookup) mysqlinput

so.. the issue ist, that i´ve got some same strings in both tables and also some names in table1, which are not included in table2.
so, first of all i want to do a lookup from table2 to table1 if you can find ids an names of table1 in table2. this i would like to do with string equal.
Issuer and id means the same (FYI).

1) if table1.Name.equals(table2.Name) && table1.Issuer == table2.id >> do nothing
2) if table1.Name.equals(table2.Name) && table1.Issuer != table2.id >> id of table 1 should get that id of table2
3) if you can´t find table1.Name in table2.Name, insert Name of table1 to table2 and set a new id for it.
i had duplicate names in table2 and i solved it with tuniqrow. ok. done.
but now i´m trying to use tmap with little successes, but actually i don´t really know how to use it the right way.
on the left hand, i´ve got my two tables, table1 and table2 and on the right, i´ve got my result table with the same attributes like in table1.
i dragged and dropped the columns of table1 to the result table: id ----> id, Issuer -----> Issuer, etc.
i dragged and dropped the columns of table2 to the result table: id ----> Issuer, Name -----> Name
in the tmapsettings on the right: catch lookup inner join is true, catch output is false.
in the expression field on the right side below the schema type i want to put in the points 1), 2), 3) like above, but i don´t know how to express to set new ids and do nothing.
another point is: should i drag and drop the Issuer and all other columns from table1 to table2?
pls help.. tmap does something, but i need the expressions.
thanks for helping me!
Nadine

[Anonymous]

hi Nadine,
first, please take a look at some of the great tutorials. Here's one that covers joining in a tMap
http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=2

If you are still running into problems after looking through the tutorials, please come back and ask.

[Anonymous]

thank you. i read all those tutorials a few times.
now, i´m back and i´m asking my questions above twice. maybe is there someone else who can answer my questions??

[alevy]

Have you joined the two tables on the left on name? With "Catch lookup inner join reject" that will give you case 3. But you then need the output from tMap to go to the database to add the new record to table 2. Make sure "Commit every" is set to 1 for the next part.
To make that new record visible to later rows from table 1, you'll have to use the "Reload at each row (cache)" lookup model. The tMap help has a good example on this.
A separate output from tMap without "Catch lookup inner join reject" but with an expression filter of table1.Issuer != table2.id will give you case 2.
You don't need to do anything for case 1.
Try it out; it's not that difficult. If you have more problems then screenprint your tMap and be specific about what's going wrong.

[Anonymous]

"A separate output from tMap without "Catch lookup inner join reject" but with an expression filter of table1.Issuer != table2.id will give you case 2."
tlogrow now shows me the names with different id´s.. in the screenshot i. e. Augustus with id 36 and id 38 has in the lookup table id 33 and i want to give Augustus now the id 33. how to do this?
in tjava you can do this with output_row = input_row.. but how to express in tmap?
now i will go on with the other steps.. step by step.. but really happy to have such guys like you here!
ps: it won´t upload the 2nd image.. i´m sry. the first table on the left is completely mapped to the output table and the expression is row1.Issuer != row2.id and on the left side commit every.
THANK YOU, alevy!!!
Nadine

[alevy]

In the tMap output tables on the right, the column name on the right becomes the left side of the assignment expression and the expression you enter against it on the left becomes the right side, essentially use the value of this expression for this column. Just drop the id from the lookup into the output.
Are you sure you've worked through the tutorials because these are really basic questions?

[Anonymous]

sry, but i didn´t understand the first sentence.
yes i did. and i spent the whole day to repeat the tutorials, but actually it still doesn´t work. i will burn another midnight oil.
thank you anyway.

[alevy]

In a screenshot like the attached, the circled row is the equivalent of putting TestResultMemberIdentifiers.TargetEntityID = Membership.ID; in a tJavaRow.
What exactly doesn't work? Screenshot your tMap and your results and explain what went wrong.
Note the size restriction on images. If you can't squeeze into those, use a third-party site and give a link.

[Anonymous]

it just gives me id´s with "0"... all id´s are "0".. although i just did what you say.
cannot share the picture id´s, they are just about 135KB, have a look at http://tinypic.com/view.php?pic=2dvpjsx&s=6
i tried it with an expressions like:
lookup.Issuer==row1.Issuer in row2 or an expression like
row1.Issuer == row2.id
it all gives me in every row Issuer "0":
id Issuer Name CoinImage Description
322 0 Augustus ... ...

[alevy]

What you've done is similar to using a SQL statement like:
SELECT row1.*, row2.ID FROM row1 LEFT JOIN row2 ON row1.Issuer=row2.id AND row1.Name=row2.Name AND row1.Description=row2.Description WHERE row2.id!=row1.Issuer
Since you've joined the two input tables on row1.Issuer=row2.id how can you ever have them != for your output condition?!
What actually happens in Talend is that the join and the output condition are evaluated separately rather than together as in the SQL statement above. If the join fails (in your example because there is no match for all three fields) then row2.id (being a non-nullable int) is treated as 0 and thus the output condition is satisfied. You need to contruct your condition appropriately.
You're also clearly not using the lookup each row model correctly as you haven't defined any globalMap keys. Read Scenario 6 of the tMap help again.
Pictures must also be no more than 1024x768 pixels and yours in 1293x485.