Hi, I am new to talend. I don't know how to get matched and unmatched rows from tmap with inner join. I have two csv files. one for customer details and another one for order details. eg: Customer.csv (Master file) CutomerId, CustomerName 101, Raj 1002, Kumar Order.csv (Lookup file, OrderDesc filed have CustomerId with other string) OrderId, OrderDesc 201, Order Booking 101 202, Order Delivered 102 203, Order Booking 103 204, Order Booking 104 I don't know to get matched and unmatched CustomerId with order csv file in tmap joins. Output Example: Matched.csv CutomerId, CustomerName, OrderId 101, Raj, 201 Unmatched.csv CutomerId, CustomerName 1002, Kumar Please help me.. Thanks Iyyappan S
The fist thing you have to do is to create in you flow for the orders a new column for the CustomerId. This can be done with simple regularly expressions. I suggest using the component tExtractRegexFields. This component should get as input the column OrderDesc. You have to create a new column called CustomerId and use this expression:
The group describes the content which the mentioned component uses for the next unused field --> you new CustomerId field. Now you have a lookup flow containing the CustomerId and you can use the possibilities of the tMap to join (and even to get the flow which does not fit to the join condition --> inner join reject)
Hi jlolling, Thank you very much for you suggest me to use tExtractRegexFields component with the Regular Expression in my job. As per your suggestion, I have changed my job. It is working fine to get matched and unmatched records. But I have encountered a problem using the regular expression on customer id in two cases . 1. Customer id is alpha numeric value 2. In the future customer id length may be varying. Yesterday I have got an idea for my job. But I don't know how to do with the talend. When I am trying to run the job it is show the below errors.Please suggest me is it possible in talend or not. Unresolved compilation problems: row2 cannot be resolved row2 cannot be resolved to a variable I have attached tMap mapping screenshot with expression builder. Is it possible in Expr.key to check master column value with current lookup column value. Is there any other way to get row2.OrderDesc value without using row2 variable name in Expr.Key field.
Thank you very much for your reply. Thanks Iyyappan S
You idea could work but not at this place! The error happens because the expression you write cannot reference to the lookup flow. Please keep in mind you are editing the right side of a Java assignment. I would put a further tMap in the lookup flow (currently row2) and transform the field here.