How do I concatenate two input Character fields

TJX Companies · Post by **TJX Companies** » Fri Dec 01, 2006 10:17 am

I am trying to take firstname and lastname fields and concatenate into a fullname in a transform stage and getting only firstname in fullname field. What am I missing here. I defined these are stage variables and deriving fullname stage variable.

This kind of concatenation worked with numeric fields in another job.

narasimha · Post by **narasimha** » Fri Dec 01, 2006 10:25 am

How are you concatenating the fields?
Try Firstname:Lastname

Raghavendra · Post by **Raghavendra** » Fri Dec 01, 2006 10:28 am

If you use Firstname: Lastname then should get the result.
I may be silly but can you check again if you have concatenated last name or not

DSguru2B · Post by **DSguru2B** » Fri Dec 01, 2006 11:25 am

Make sure you have properly defined the LastName variable as char or varchar.

TJX Companies · Post by **TJX Companies** » Fri Dec 01, 2006 12:18 pm

narasimha wrote:How are you concatenating the fields?
Try Firstname:Lastname

That is what I am trying. I am deriving the fullname by 'Svfirstname : Svlastname' and still getting only first name in the fullname field. All the stage variables are defined as varchar.

narasimha · Post by **narasimha** » Fri Dec 01, 2006 12:47 pm

Trim your input and check for NULL before concatenating. See if it helps

TJX Companies · Post by **TJX Companies** » Fri Dec 01, 2006 1:10 pm

[quote="narasimha"]Trim your input and check for NULL before concatenating. See if it helps

Hi Narasimha, I tried with Trim too. It didn't worked. Is there any specific trim function I should be using ? I tried 'Trim' and 'TrimLeadingTrailing' and both didn't work.

narasimha · Post by **narasimha** » Fri Dec 01, 2006 1:33 pm

Lets get some more details here.

What is your source? (File, DB,...)
Also can you post the exact derivation you have used for your Stage Variables.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 01, 2006 2:23 pm

Nothing should be in quotes.

Create another output link from the Transformer stage containing the values from the stage variables and the value from the derivation. Run this into, say, a Peek stage; inspect the results and diagnose.

You may discover (for example) that LastName is truly empty in some of your source records.

TJX Companies · Post by **TJX Companies** » Fri Dec 01, 2006 2:25 pm

[quote="narasimha"]Lets get some more details here.

What is your source? (File, DB,...)
Also can you post the exact derivation you have used for your Stage Variables.

My source is output from FTP_Plugin from a Windows Server ( Excel Spread sheet). How do I attach the screen shot here ? Or do I need to e-mail you the screen shot ( This is first time I am in a forum ).

TJX Companies · Post by **TJX Companies** » Fri Dec 01, 2006 2:34 pm

ray.wurlod wrote:Nothing should be in quotes.

Create another output link from the Transformer stage containing the values from the stage variables and the value from the derivation. Run this into, say, a Peek stage; inspect the results and diagnose.

You may discover (for example) that LastName is truly empty in some of your source records.

Hi, That is what I am actually doing to diagnose the problem. I am creating a output with just the fullname, firstname and lastname. The first and last name stage variables are defined as 20 varchar but when I tried to find the length I am getting 20. It is as if the white space is counted too. I tried all options like (Whitespace, Trim, TrimLeadingTrailing) and all of these are returning back the length of these fields as 20. This is what is confusing me a lot.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 01, 2006 3:07 pm

How are you asking for the length to be reported? Create more output columns - define their derivations to use the Len() function.

Is the output file fixed-width format? If so, the fields will always be padded to length.

TJX Companies · Post by **TJX Companies** » Fri Dec 01, 2006 3:52 pm

ray.wurlod wrote:How are you asking for the length to be reported? Create more output columns - define their derivations to use the Len() function.

Is the output file fixed-width format? If so, the fields will always be padded to length.

Yes. The output file is fixed width format. That explains why I am getting the length that way. I will try your suggestion and get back to you. Thanks a lot.

ray.wurlod · Post by **ray.wurlod** » Sat Dec 02, 2006 4:10 am

TJX Companies wrote:This kind of concatenation worked with numeric fields in another job.

Just noticed this in the original post. Which suggests to me that the other job was a server job.

TJX Companies · Post by **TJX Companies** » Sat Dec 02, 2006 10:57 am

ray.wurlod wrote:
TJX Companies wrote:This kind of concatenation worked with numeric fields in another job.
Just noticed this in the original post. Which suggests to me that the other job was a server job.

Hi, The job which worked is a parallel job. But the Input Sequential file is coming from a server job. Does that make any difference ?

DSXchange

How do I concatenate two input Character fields

How do I concatenate two input Character fields

Re: How do I concatenate two input Character fields

Re: How do I concatenate two input Character fields