topic Re: tmap, lookup, insert new rows in Talend Studio

tmap, lookup, insert new rows

Anonymous — Tue, 11 Sep 2012 16:42:58 GMT

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

Re: tmap, lookup, insert new rows

Anonymous — Tue, 11 Sep 2012 20:41:59 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Tue, 11 Sep 2012 20:53:27 GMT

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

Re: tmap, lookup, insert new rows

alevy — Wed, 12 Sep 2012 04:03:40 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Wed, 12 Sep 2012 10:29:59 GMT

"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

Re: tmap, lookup, insert new rows

alevy — Wed, 12 Sep 2012 13:59:44 GMT

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?

Re: tmap, lookup, insert new rows

Anonymous — Wed, 12 Sep 2012 19:08:29 GMT

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.

Re: tmap, lookup, insert new rows

alevy — Thu, 13 Sep 2012 01:16:03 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Thu, 13 Sep 2012 03:15:51 GMT

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

Re: tmap, lookup, insert new rows

alevy — Thu, 13 Sep 2012 04:31:57 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Thu, 13 Sep 2012 16:01:28 GMT

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

Re: tmap, lookup, insert new rows

Anonymous — Wed, 19 Sep 2012 17:58:22 GMT

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?

Re: tmap, lookup, insert new rows

alevy — Thu, 20 Sep 2012 11:20:17 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Wed, 26 Sep 2012 06:30:13 GMT

it´s all working now! made my day.

Re: tmap, lookup, insert new rows

Anonymous — Tue, 19 Aug 2014 09:19:36 GMT

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

Re: tmap, lookup, insert new rows

Anonymous — Tue, 19 Aug 2014 09:40:31 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Tue, 19 Aug 2014 09:58:41 GMT

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?

Re: tmap, lookup, insert new rows

Anonymous — Tue, 19 Aug 2014 10:12:23 GMT

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"))

Re: tmap, lookup, insert new rows

Anonymous — Tue, 19 Aug 2014 10:20:16 GMT

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.

Re: tmap, lookup, insert new rows

Anonymous — Tue, 19 Aug 2014 10:23:02 GMT

can you show me tMap screen shot and the error code