Four Stars

Problem in searching a substring within a String

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

6 REPLIES
Employee

Re: Problem in searching a substring within a String

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

 

 

Four Stars

Re: Problem in searching a substring within a String

@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 ",".

Four Stars

Re: Problem in searching a substring within a String

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

Highlighted
Employee

Re: Problem in searching a substring within a String

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

Employee

Re: Problem in searching a substring within a String

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.

 

Four Stars

Re: Problem in searching a substring within a String

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