topic Re: tMap with InnerJoin and Rejected Rows in Talend Studio

tMap with InnerJoin and Rejected Rows

Anonymous — Sat, 16 Nov 2024 11:46:10 GMT

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

Re: tMap with InnerJoin and Rejected Rows

Anonymous — Wed, 29 Jan 2014 22:01:16 GMT

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:

"({1,4})"

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)

Re: tMap with InnerJoin and Rejected Rows

Anonymous — Thu, 30 Jan 2014 07:19:08 GMT

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

Re: tMap with InnerJoin and Rejected Rows

Anonymous — Thu, 30 Jan 2014 22:09:48 GMT

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.

Re: tMap with InnerJoin and Rejected Rows

Anonymous — Fri, 31 Jan 2014 05:32:04 GMT

Hi jlolling,
Thank you very much. I have completed my job with tExtractRegexFields component.
Thanks
Iyyappan S