topic Re: Problem in searching a substring within a String in Data Quality

Problem in searching a substring within a String

Anonymous — Thu, 10 Aug 2017 12:59:49 GMT

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?

Re: Problem in searching a substring within a String

Anonymous — Mon, 21 Aug 2017 03:05:21 GMT

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

Re: Problem in searching a substring within a String

Anonymous — Tue, 22 Aug 2017 14:06:49 GMT

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

Re: Problem in searching a substring within a String

Anonymous — Wed, 30 Aug 2017 16:08:47 GMT

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

Re: Problem in searching a substring within a String

Anonymous — Sat, 09 Sep 2017 13:35:54 GMT

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

Re: Problem in searching a substring within a String

dprot — Sat, 09 Sep 2017 13:43:52 GMT

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.

Re: Problem in searching a substring within a String

Anonymous — Mon, 11 Sep 2017 09:24:48 GMT

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