topic Re: Contains Matching? in Talend Studio

Contains Matching?

Anonymous — Sat, 16 Nov 2024 04:21:55 GMT

Hi. I'm pretty new to Talend and looking to do the following:

I have a list of filename gathered from the directory using a tFileList and outputted to a MSSQL DB table called "Reports"

e.g.

ID | FileName

1 | ABC_000_ACC12345_Rep1.pdf

2 | DE_ACC35345_Rep2.pdf

3 | DE_123_ACC12345_Report.pdf

etc

I then have a mapping file which has client IDs and Account Numbers in it. I have outputted this to another SQL table called "Accounts"

e.g.

ID | Name | AccountNumber

1 | Joe Bloggs | ACC12345

2 | John Doe | ACC35345

What I need is to join the reports from the "Reports" table, to the relevant account from the "Accounts" table by looking for the "AccountNumber" in the filename strings from the "Reports" table.

With the examples shown above, my aim is to get a final output like this:

ReportID | ClientID | AccountNumber | Filename

1 | 1 | ACC12345 | ABC_000_ACC12345_Rep1.pdf

2 | 2 | ACC35345 | DE_ACC35345_Rep2.pdf

3 | 1 | ACC12345 | DE_123_ACC12345_Report.pdf

Does Talend have any functions etc that can use one set of data almost as a contains match lookup? Is anybody able to help?

Steve

Re: Contains Matching?

lizzy1 — Wed, 16 Oct 2019 17:37:59 GMT

Use a filter expression in your TMap with the java function String.contains()

Re: Contains Matching?

Anonymous — Wed, 16 Oct 2019 22:25:47 GMT

Hi,

Please refer the screenshot below.

Please note that while using contain function, you need to make sure that you are trimming all the extra spaces while reading the data from file (By selecting tFileInputDelimited -> Advanced settings-> Trim all columns

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Contains Matching?

Anonymous — Thu, 17 Oct 2019 14:38:53 GMT

Thanks Nikhil, that's great. Very much appreciated. Being a SQL developer and not a Java developer, and being new to Talend, I am getting there... slowly

I've now got my job to work as I want it

Re: Contains Matching?

Anonymous — Thu, 17 Oct 2019 14:43:01 GMT

@abouzigon

No worries. If you ask my background, I also started my professional journey from Oracle SQL 🙂

So its just a matter of time before you also become a master in Talend!

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved