One Star

tmap, lookup, insert new rows

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
30 REPLIES

Re: tmap, lookup, insert new rows

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.
One Star

Re: tmap, lookup, insert new rows

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??
Seven Stars

Re: tmap, lookup, insert new rows

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.
One Star

Re: tmap, lookup, insert new rows

"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
Seven Stars

Re: tmap, lookup, insert new rows

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?
One Star

Re: tmap, lookup, insert new rows

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.
Seven Stars

Re: tmap, lookup, insert new rows

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.
One Star

Re: tmap, lookup, insert new rows

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 ... ...
Seven Stars

Re: tmap, lookup, insert new rows

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.
One Star

Re: tmap, lookup, insert new rows

thank you for your help.
now, first i will go on with the tutorials or "i have to", because i just read them.. Smiley Wink you´re right. maybe then i will come back!
have a nice day!
nadine
One Star

Re: tmap, lookup, insert new rows

it´s partly working now.
but i still have some trouble, pls have a look at the screenshots.
i want to set new id´s to picture one. i. g. 7 to 6 and so on (the last picture is the lookup table, Name Aedui with id6, for comparison only. "Issuer" of the 1st picture corresponds to "id" of the last picture)
the second picture is the corresponding tmap. you can see the output in picture 3.
if i do the lookup on name2.. the upcoming picture 5 shows me that the join failed - again (the corresponding tmap in picture 4)
you can find all the names (name1 AND name2) included in the lookup table.. the join couldn´t fail in fact.
i´m at a loss now. i cannot understand why it´s working on name1 and why it won´t work on name2?
Seven Stars

Re: tmap, lookup, insert new rows

Looking at picture 5, I think you'll find the problem is trailing spaces in your source but not in your lookup. Unless you have good reason, I always recommend changing the advanced setting of each input component to "Trim all columns" to avoid this.
One Star

Re: tmap, lookup, insert new rows

it´s all working now! made my day.
One Star

Re: tmap, lookup, insert new rows

hi everybody,
i want to filter only those record which was stored yesteday can someone please tell me How can i fetch that records using Created date field?
thank you,
Regards ,
Sam
Five Stars

Re: tmap, lookup, insert new rows

can you provide more details like. what is source, where do you want to use filter so on. 
but if it is database or file then there is opportunity to filter data base on date. 
you can use TalendDate.getDate()-1  function which will return you yesterdays date. then use this date for filter. 
One Star

Re: tmap, lookup, insert new rows

hi Everybody,
i have fetched two modules from salesforce and have to join and extract data which uploaded last day
can u please tell me which condition may i used for it?
Five Stars

Re: tmap, lookup, insert new rows

you can use below code in tmap at right side Expression filter field. 
TalendDate.getCurrentDate().after(TalendDate.addDate(TalendDate.getCurrentDate(), -1, "DD"))

in above case use row2.CreatedDate for filter. 
TalendDate.getCurrentDate().after(row2.CreatedDate.addDate(TalendDate.getCurrentDate(), -1, "DD"))
One Star

Re: tmap, lookup, insert new rows

hi Umesh sir,
As you sugest i have used the Expression 
TalendDate.getCurrentDate().after(TalendDate.addDate(TalendDate.getCurrentDate(), -1, "DD"))
in Filtered_Data table but it gives the error Quote cannot be resolved to a variable
tell me the problem
tahnk you.
Five Stars

Re: tmap, lookup, insert new rows

can you show me tMap screen shot and the error code
One Star

Re: tmap, lookup, insert new rows

ok sir,
screenshots is here
One Star

Re: tmap, lookup, insert new rows

hi Everybody,
may actual problem is that a have to integrate the data from Quote and opportunity module into an csv file but the data should be last day record can u pls send me the whatever procedure required for it.
in a job i already get the salsforce moules,TMap and TFileOutputDelimiter and i only want the expression required to perform this operation.
Regards,
Sam
Five Stars

Re: tmap, lookup, insert new rows

you still get the same error? as per your tMap setting and expression provided by me should work for you. 
One Star

Re: tmap, lookup, insert new rows

i want to join three Salesforce Entities and using join operation have to fetch some records but it throw some Types of Exception so What can i do for it?
Five Stars

Re: tmap, lookup, insert new rows

write below code as it is and check. 
row1.CreatedDate.after(TalendDate.addDate(TalendDate.getCurrentDate(), -1, "DD"))
One Star

Re: tmap, lookup, insert new rows

hello Umesh Sir,
As per your Suggestion i used the Expression in row1 Expression And it Works Now,
My Questions is that if we want to fetch the last day record from one table using its date for eg.CloseDate, but now we have to give another condition i.e. where product="Watch" so what expression it requires and where should be placed
Thank you,
Regards,
Sam
One Star

Re: tmap, lookup, insert new rows

hello EveryBody,
can u please resolved this Error
Starting job Module1 at 17:38 25/08/2014.
connecting to socket on port 3902
connected
Unknown column 'system_pid' in 'field list'Exception in component tMap_1
java.lang.NullPointerException
at salesforce_connection.module1_0_1.Module1.tSalesforceInput_1Process(Module1.java:3724)
at salesforce_connection.module1_0_1.Module1.runJobInTOS(Module1.java:15788)
at salesforce_connection.module1_0_1.Module1.main(Module1.java:15627)
Unknown column 'priority' in 'field list'Unknown column 'system_pid' in 'field list'
disconnected
Job Module1 ended at 18:06 25/08/2014.

Five Stars

Re: tmap, lookup, insert new rows

it is very helpfull if you upload tMap mapping because, you have Mapped one column named with "system_pid" which is not present in row1, row2, or row3 or you forgot to add row1,2,3 with same column. check and confirm.
One Star

Re: tmap, lookup, insert new rows

hi Umesh,
there is not any type of system_pid coloumn mapping and i checked in my tMap
please suggest any other idea
thanks.
Five Stars

Re: tmap, lookup, insert new rows

i am suspecting it must be in filter data section please check there.