topic Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap in Talend Studio

Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

Anonymous — Tue, 12 Mar 2019 16:29:52 GMT

I have an input column that has Primary Salutation of a mailing address for example "Steve and Rachel Smith" is one value. If I needed to manipulate that value to pull just "Steve" for a First Name Output Column. What String Method could I use to Output just the first word in that value? This will be used across the board. Also I would need to pull the Last Name for a Last Name Field "Smith" would be the value I grab.

Now there are different input values in different formats in this file. For example there are input values in this format listed below as well.

"Steve"

"Steve and Rachel"

"Steve Smith"

So the String Method isn't cut and dry there are a few different outputs that would cause issues with how the other strings are manipulated.

I know for the first name it would be the First Word before the first space.

Second First Name would be the first word after the word "and"

The Last Name would be the second word if no "and" exists, or the fourth word if the word "and" exists.

If anyone can help with this, my Java String Method coding is not there yet

-Andrew

Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

DataTeam1 — Tue, 12 Mar 2019 20:49:14 GMT

Try this

String test = "Steve and Rachel";
System.out.println("Firstname: " + test.substring(0, test.indexOf(" ")));
System.out.println("LastName: " + test.substring(test.lastIndexOf(" ")+1));

Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

akumar2301 — Wed, 13 Mar 2019 10:57:36 GMT

PFA a sample job

Count Number of ( And/AND/and/&) in the string

If CountAnd is Zero

Count Number of Word in String

If CountNumberofWord = 1

FirstName is 1st Word , 2nd Name is ""

If CountNumberofWord > 1

FirstName is 1st Word , 2nd Name is last word

If CountAnd > 0

Count Number of Word in (last part of String separated by And)

If CountNumberofWord = 1

FirstName is 1st Word , 2nd Name is ""

If CountNumberofWord > 1

FirstName is 1st Word , 2nd Name is last word

fggg_0.1.zip

Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

Anonymous — Wed, 13 Mar 2019 14:12:51 GMT

Hey @uganesh

Thanks for the solution! one more scenario that I am dealing with within this column

If the Name Line is in this format "Smith, Steve". So Last Name is listed first followed by a comma, then First Name.

This can be a seperate String Method you don't have to include it within your last job.

Last Name = "Smith"

First Name = "Steve"

Thanks,

Andrew

Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

akumar2301 — Wed, 13 Mar 2019 14:24:41 GMT

@sm

Best solution is to ask your provider to clean file himself. These input cannot be system generated.

String input= "Smith, Steve"

FirstName = (input == null)? "" : (input.split(",").length > 1 ? input.split(",")[1] : "")

Last Name = (input == null)? "" : (input.split(",").length > 1 ? input.split(",")[0] : "")

Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

Anonymous — Wed, 13 Mar 2019 14:59:54 GMT

@uganesh

We're in the process for asking are clients to standardize their data to one specific format. It's been a headache especially coming from a SQL background and not knowing much Java. Do you have any good Java script resources, to learn what each character means within the code?

Thanks,

Andrew

Re: Grabbing the First Value From an Input String based off a space or "and" value to use in multiple output columns in tMap

Anonymous — Wed, 13 Mar 2019 18:29:49 GMT

@sm

Hi Andrew,

I am also from SQL background and not a Java developer. But Talend has helped me to solve lot of issues with basic Java skill set. So you can easily become a Talend professional without deep java knowledge 🙂

Coming to the current issue, I agree with @uganesh 200%. This is an issue created by source. So they have to first cleanse the data at source and then they have to transport the data to other systems.

"Steve"
"Steve and Rachel"
"Steve Smith"
"Smith, Steve"

It seems they have opened just a free text box in the front end system or direct load to a excel sheet and asking us to clear all the junk data. If they are writing all these combinations, I am sure the customer can write the data in below format also in that system 🙂

Let me test Andrew again and I am sure our source system team can beat Mr.Smith this time

And they might be expecting to pick Andrew Smith from this data 🙂

There are Data Quality elements in tMap as shown below but even those will get beaten due to all different combinations in your input data

So we need to push it back and ask the source data owner to either cleanse and give it or ask them to sign off as an agreed data risk from source.

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 🙂