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