Hi, hope everybody is doing well. Need your help.
I am trying to load a datastage server job which has one look up table.
It structures this way,
Address look up------
Input ---------Transformer------Output
Now, there is a column "street address" which populates data Add1(varchar,100) and Add2(varchar,70)from "Address look up table".
I used a concatenate function in transformer saying " Streetaddress = add1+ " "+add2. The output Street address column data type is Varchar,256 which is linked to Address Look up table.
When I use concatenate of above , it is not adding anything keeping the column null. When I join "add1", it is working fine adding only "add1" column. Not sure why it is not adding all two columns,, Any help
Thanks,
Joining columns in Transformer
Moderators: chulett, rschirm, roy
Thanks Craig. I actually can't see the whole explanation as it is locked though I have premium membership. I have sent an email to the editor, hoping it will be solved very soon.
Now, NullToEmpty returns an empty string if the input column is null, otherwise it returns the input column value.right
The issue is that, Add1 suppose :272, Ohio Street, and Add2 is "Suite 200.
So when I concatenate,it should be "272,Ohio St Suite200. In many location code which I am joining with input location code, add2 has value, some of them add2 does not have value.
Now, NullToEmpty returns an empty string if the input column is null, otherwise it returns the input column value.right
The issue is that, Add1 suppose :272, Ohio Street, and Add2 is "Suite 200.
So when I concatenate,it should be "272,Ohio St Suite200. In many location code which I am joining with input location code, add2 has value, some of them add2 does not have value.
I assume you mean the concatenated value you require in the example is:
"272, Ohio Street, Suite 200"
in which case you would concatenate Add1, followed by a space followed by NullToEmpty(Add2)
After that I do not understand what the issue is.
Are you using the result of this concatenation to match to values in a lookup?
If Add2 is empty the result in the above example would be "272, Ohio Street, " though you would presumably not append the space in this case.
Is the issue that when the 2nd field is empty it doesn't match any value in the reference data lookup? If that's the case you'll need to be more specific about the requirement in these circumstances.
"272, Ohio Street, Suite 200"
in which case you would concatenate Add1, followed by a space followed by NullToEmpty(Add2)
After that I do not understand what the issue is.
Are you using the result of this concatenation to match to values in a lookup?
If Add2 is empty the result in the above example would be "272, Ohio Street, " though you would presumably not append the space in this case.
Is the issue that when the 2nd field is empty it doesn't match any value in the reference data lookup? If that's the case you'll need to be more specific about the requirement in these circumstances.
Sorry, I should have said something... noticed the strange "+" syntax but figured you were posting "psuedo-code" rather than what you were actually doing (math) especially when you said the end result was NULL in the first post rather than zero. My advice still stands, however, if you need to concatentate strings together and any of them can be NULL.
Don't forget you can also do conditional statements inside the concatenation operators when needed:
Should be close to correct. Very similar to what the NullToEmpty does under the covers but only adds the space when the second address exists.
Don't forget you can also do conditional statements inside the concatenation operators when needed:
Code: Select all
Addr1 : (If IsNull(Addr2) then '' else ' ' : Addr2)-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers