Problem in searching a substring within a String

[Anonymous]

Dear All, 

I have a scenario where i have to search for customer ID in the Description column of Bank.

I have two input files: Bank and Customer_master.

Bank description has data something like this : WP/JULY/11D2135E/Interest/Kelie where 11D2135E is the customerID.

I am using following method:

Bank.Description.contains(Customer_master.ACCOUNT_NUMBER)  

However it is giving me correct results but it is failing in some particular scenarios like.

Customer ID : 21AD210001, 10001

Bank Description: WP/AUG/SAL/21AD210001/WASA

in this case it should only match with 21AD210001 but it is also matching with 10001 which is incorrect.

Any idea how to overcome with this issue?

[Anonymous]

Well, you should truncate your Customer ID and remove everything after the ,  first.  That should be a preprocessing on the file before you do the search.

You should do a

Bank.Description.contains(Customer_master.ACCOUNT_NUMBER.indexOf(',') > 0 ? (Customer_master.ACCOUNT_NUMBER.substring(0, Customer_master.ACCOUNT_NUMBER.indexOf(',')) : Customer_master.ACCOUNT_NUMBER))

This assumes all values are not null.  If they are null, you should add proper check for null

[Anonymous]

@iburtally , thanks for your suggestion but i think you have misunderstood the scenario.

Customer ID : 21AD210001, 10001 are two separate rows not one.

So there is no point in removing everything after ",".

[Anonymous]

Hello

The scenario still not too clear for me..

based on your description, it seams you need "top 1" of the matches... is it?

if it is, a possible solution i found is use tAggregrateRow (see capture1.jpg)

Muzio

Capture.JPG

[Anonymous]

I am not sure I understand it properly.  Please provide files sample and your sample job.

[dprot]

Hi,

Why are you using a "contains" function ? If it is an exact String that you are looking for, I think you should rather use an "equals" function.

[Anonymous]

Hello...

I think I've finally understand the scenario..

Please don't care about my previous suggestion. The right think to do is NOT to filter "TOP 1" of the rows.

Easiest solution should be: 

Bank.Description.contains("/" + Customer_master.ACCOUNT_NUMBER + "/")

Anyway it could result in a wrong match....

ex: 

Customer ID : 21AD210001, 10001

Bank Description: WP/AUG/10001/21AD210001/WASA

This case will result in 2 rows even if you expect only one.

Other solution could be to  arrange Bank description in a fully defined structure splitted by "/" (using tExtractDelimitedFileds component)

This will allow you to add some more fields in the main structure:

Bank.Description: WP/AUG/10001/21AD210001/WASA

Bank.Des1: WP

Bank.Des2: AUG

Bank.Des3: 10001

Bank.CusId: 21AD210001

Bank.Des5: WASA

than use tmap to join main flow (Banks structure) with lookup  flow (Customers) joining Customer_master.ACCOUNT_NUMBER with Bank.CusId

Hope this may help you

Muzio