topic Concatenating With IF THEN ELSE Syntax in Talend Studio

Concatenating With IF THEN ELSE Syntax

Anonymous — Tue, 26 Feb 2019 16:23:55 GMT

Hello,

I am dealing with an Input file with a Separate Column for Area Code and Phone Number I combined them together by concatenating them

row2.AREA_CODE + row2.PHONE

Now Instead of having blank values for records without area codes and phone numbers the input file has both "000" for Area Code and "0000000" for Phone Number.

If I wanted to build a String Method in my tMap Output Expression for the Field Phone Number How Would I leave the Field Blank if both Area Code and Phone Number had "000" or "0000000"? I obviously want to pass through the values that actual contain number values for these two fields.

Please Advise

Thanks,

Andrew

Re: Concatenating With IF THEN ELSE Syntax

akumar2301 — Tue, 26 Feb 2019 16:39:07 GMT

Try something link this

(row2.AREA_CODE == null || Integer.parseInt(row2.AREA_CODE) == 0 ? "" : row2.AREA_CODE +
row2.PHONE == null || Integer.parseInt(row2.PHONE) == 0 ? "" : row2.PHONE;

Re: Concatenating With IF THEN ELSE Syntax

Anonymous — Tue, 26 Feb 2019 16:46:31 GMT

You need to be a bit more specific with this. Can you clarify....

1) Will the output be null or an empty String if the requirements are not met?

2) Do BOTH AREA_CODE and PHONE have to have values for them to be combined? For example, which of the following are acceptable...

000+123456768 = 00012345678

123+00000000 = 12300000000

000+00000000 = null

000+00000000 = ""

123+00000000 = null

123+00000000 = ""

000+1234567890 = null

000+1234567890 = ""

123+12345678 = 12312345678

Re: Concatenating With IF THEN ELSE Syntax

Anonymous — Tue, 26 Feb 2019 17:46:32 GMT

Hi Rhall,

1) An empty String is what I am looking for the final Output Field which will be Phone Number

2)The Area Code + Phone Number Field have to be combined

For Instance

123+4567890 Would turn into 1234567890

If the Area Code value is "000" then the Phone Number Field will be "0000000" as well. So I'm trying to remove the 0's from the output and only have empty strings

123+1234567 = 1231234567

000+00000000 = ""

You won't see any of the other values/combinations you listed in the input file

Re: Concatenating With IF THEN ELSE Syntax

Anonymous — Tue, 26 Feb 2019 18:04:48 GMT

Hello Kumar,

Your current String Method is not outputting the Area Code just the 7 Digit Phone Number. I do notice the 0's in the area code and phone number are not appearing. Your close but not quite there.

Thanks,

Andrew

Re: Concatenating With IF THEN ELSE Syntax

Anonymous — Tue, 26 Feb 2019 22:26:06 GMT

If that's the case (and you are sure there will be no nulls coming in) the solution will be this....

row2.AREA_CODE.equals("000") || row2.PHONE.equals("0000000") ? "" : row2.AREA_CODE + row2.PHONE

Re: Concatenating With IF THEN ELSE Syntax

Anonymous — Wed, 27 Feb 2019 03:36:08 GMT

Hi Andrew,

@rhall and @uganesh has already shown you the method to replace the values based on null expressions. One another item I would like to introduce is a component to Standardize phone numbers (tStandardizePhoneNumbers).

This component had helped me in one of my previous UK projects to standardize the phone number to common format and flag any invalid of impossible phone numbers. It can be used for other countries also.

The help document link to the component is as shown below.

https://help.talend.com/reader/hCrOzogIwKfuR3mPf~LydA/8_Q4WAzPdmlNfO5mdguPVg

Could you please pass the values through this component and see whether it is helping in data quality checks of your incoming data? You need to merge the data as specified in previous posts. Once the incoming data is ready, the suggestion will be to pass the data through this component to make sure that it is getting enriched further.

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 🙂