concatenation of null values
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
concatenation of null values
I am having the following setup
Input
BISEPS_RETAILER
1
2
3
LOOKUP HASH(7 columns)
AD_ID | COMPANY |SUBURB|POSTCODE|FIRSTNAME|SURNAME|TELEPHONE
condition is if input.biseps_retailer is matching with ad_id in lookup hash then I need to concatenate company+suburb+postcode+firstname+surname+telephone.
The problem here is some columns either suburb or postcode or firstname can be null. So If I concatenate the other values with null , the result is null. In the new file for lookup, I can't say particularly that this column will be null. So I need to know how to write the expression which checks the null columns and add the rest in order to get the net result.
Please guide me
Input
BISEPS_RETAILER
1
2
3
LOOKUP HASH(7 columns)
AD_ID | COMPANY |SUBURB|POSTCODE|FIRSTNAME|SURNAME|TELEPHONE
condition is if input.biseps_retailer is matching with ad_id in lookup hash then I need to concatenate company+suburb+postcode+firstname+surname+telephone.
The problem here is some columns either suburb or postcode or firstname can be null. So If I concatenate the other values with null , the result is null. In the new file for lookup, I can't say particularly that this column will be null. So I need to know how to write the expression which checks the null columns and add the rest in order to get the net result.
Please guide me
Sujatha K
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
Just use it directly in the concatenation for the fields. Worst case:
Obviously not syntactically correct without the link name.
Code: Select all
NullToEmpty(company):NullToEmpty(suburb):NullToEmpty(postcode):NullToEmpty(firstname):NullToEmpty(surname):NullToEmpty(telephone)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
Yes . I have done like below as you said
"
if Biseps_Retailer.BISEPS_RETAILER = -1 then @NULL
else
if Biseps_Retailer.BISEPS_RETAILER > 0 then
hash_Biseps_Retailer.AD_COMPANY:",":
NullToEmpty(hash_Biseps_Retailer.AD_BRANCH):",":
NullToEmpty(hash_Biseps_Retailer.AD_STREET):",":
hash_Biseps_Retailer.AD_SUBURB:",":
hash_Biseps_Retailer.AD_STATE:",":
hash_Biseps_Retailer.AD_POSTCODE:",":
NullToEmpty(hash_Biseps_Retailer.AD_TELEPHONE):",":
NullToEmpty(hash_Biseps_Retailer.AD_CONTACTFIRSTNAME):",":
NullToEmpty(hash_Biseps_Retailer.AD_CONTACTSURNAME)
else
Biseps_Retailer.BISEPS_RETAILER
Result I got is
Farnell Electronic Components,Component Sales,72 Ferndell Street,CHESTER HILL,NSW,2162,1300 361 005,,
How to avoid the comma at the end if the contactfirstname is null?
"
if Biseps_Retailer.BISEPS_RETAILER = -1 then @NULL
else
if Biseps_Retailer.BISEPS_RETAILER > 0 then
hash_Biseps_Retailer.AD_COMPANY:",":
NullToEmpty(hash_Biseps_Retailer.AD_BRANCH):",":
NullToEmpty(hash_Biseps_Retailer.AD_STREET):",":
hash_Biseps_Retailer.AD_SUBURB:",":
hash_Biseps_Retailer.AD_STATE:",":
hash_Biseps_Retailer.AD_POSTCODE:",":
NullToEmpty(hash_Biseps_Retailer.AD_TELEPHONE):",":
NullToEmpty(hash_Biseps_Retailer.AD_CONTACTFIRSTNAME):",":
NullToEmpty(hash_Biseps_Retailer.AD_CONTACTSURNAME)
else
Biseps_Retailer.BISEPS_RETAILER
Result I got is
Farnell Electronic Components,Component Sales,72 Ferndell Street,CHESTER HILL,NSW,2162,1300 361 005,,
How to avoid the comma at the end if the contactfirstname is null?
Sujatha K
First mention of there being a delimiter involved.
Use an expression inside each field that can be null, check for null and only add the comma when it is not. Rather than:
Something like this instead:
However, better to prefix the comma in front of non-empty fields rather than stick one on the end when you don't know if another field is coming. However, you could always trim off any trailing comma.
Use an expression inside each field that can be null, check for null and only add the comma when it is not. Rather than:
Code: Select all
: NullToEmpty(hash_Biseps_Retailer.AD_CONTACTFIRSTNAME) : "," :
Code: Select all
: (If IsNull(hash_Biseps_Retailer.AD_CONTACTFIRSTNAME) Then "" Else hash_Biseps_Retailer.AD_CONTACTFIRSTNAME:",") :
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
The problem is resolved and I did like below
I created a routine
aaifNullReturn(Argument,Result,alternate)
Created stage variables like below inorder to concatenate with earlier values.
1)aaIfNullReturn(hash_Biseps_Retailer.AD_COMPANY,"",hash_Biseps_Retailer.AD_COMPANY)-stage variable -->COMPANY
2) aaIfNullReturn(hash_Biseps_Retailer.AD_BRANCH,COMPANY,COMPANY:",":hash_Biseps_Retailer.AD_BRANCH)--STAGE VARIABLE-->BRANCH
3) aaIfNullReturn(hash_Biseps_Retailer.AD_STREET,BRANCH,BRANCH:",":hash_Biseps_Retailer.AD_STREET)-STAGE VARIABLE - STREET.
.
.
aaIfNullReturn(hash_Biseps_Retailer.AD_CONTACTSURNAME,FIRSTNAME,FIRSTNAME:",":hash_Biseps_Retailer.AD_CONTACTSURNAME)->STAGE VARIABLE-SURNAME
And finally in transformer I used the condition like below:
if Biseps_Retailer.BISEPS_RETAILER = -1 then @NULL
else
if Biseps_Retailer.BISEPS_RETAILER > 0 then
SURNAME
else @NULL
Thanks a lot, Chulett for your guidance.
I created a routine
aaifNullReturn(Argument,Result,alternate)
Created stage variables like below inorder to concatenate with earlier values.
1)aaIfNullReturn(hash_Biseps_Retailer.AD_COMPANY,"",hash_Biseps_Retailer.AD_COMPANY)-stage variable -->COMPANY
2) aaIfNullReturn(hash_Biseps_Retailer.AD_BRANCH,COMPANY,COMPANY:",":hash_Biseps_Retailer.AD_BRANCH)--STAGE VARIABLE-->BRANCH
3) aaIfNullReturn(hash_Biseps_Retailer.AD_STREET,BRANCH,BRANCH:",":hash_Biseps_Retailer.AD_STREET)-STAGE VARIABLE - STREET.
.
.
aaIfNullReturn(hash_Biseps_Retailer.AD_CONTACTSURNAME,FIRSTNAME,FIRSTNAME:",":hash_Biseps_Retailer.AD_CONTACTSURNAME)->STAGE VARIABLE-SURNAME
And finally in transformer I used the condition like below:
if Biseps_Retailer.BISEPS_RETAILER = -1 then @NULL
else
if Biseps_Retailer.BISEPS_RETAILER > 0 then
SURNAME
else @NULL
Thanks a lot, Chulett for your guidance.
Sujatha K
-
- Premium Member
- Posts: 102
- Joined: Tue Jan 31, 2006 4:13 am
The problem is resolved and I did like below
I created a routine
aaifNullReturn(Argument,Result,alternate)
Created stage variables like below inorder to concatenate with earlier values.
1)aaIfNullReturn(hash_Biseps_Retailer.AD_COMPANY,"",hash_Biseps_Retailer.AD_COMPANY)-stage variable -->COMPANY
2) aaIfNullReturn(hash_Biseps_Retailer.AD_BRANCH,COMPANY,COMPANY:",":hash_Biseps_Retailer.AD_BRANCH)--STAGE VARIABLE-->BRANCH
3) aaIfNullReturn(hash_Biseps_Retailer.AD_STREET,BRANCH,BRANCH:",":hash_Biseps_Retailer.AD_STREET)-STAGE VARIABLE - STREET.
.
.
aaIfNullReturn(hash_Biseps_Retailer.AD_CONTACTSURNAME,FIRSTNAME,FIRSTNAME:",":hash_Biseps_Retailer.AD_CONTACTSURNAME)->STAGE VARIABLE-SURNAME
And finally in transformer I used the condition like below:
if Biseps_Retailer.BISEPS_RETAILER = -1 then @NULL
else
if Biseps_Retailer.BISEPS_RETAILER > 0 then
SURNAME
else @NULL
Thanks a lot, Chulett for your guidance.
I created a routine
aaifNullReturn(Argument,Result,alternate)
Created stage variables like below inorder to concatenate with earlier values.
1)aaIfNullReturn(hash_Biseps_Retailer.AD_COMPANY,"",hash_Biseps_Retailer.AD_COMPANY)-stage variable -->COMPANY
2) aaIfNullReturn(hash_Biseps_Retailer.AD_BRANCH,COMPANY,COMPANY:",":hash_Biseps_Retailer.AD_BRANCH)--STAGE VARIABLE-->BRANCH
3) aaIfNullReturn(hash_Biseps_Retailer.AD_STREET,BRANCH,BRANCH:",":hash_Biseps_Retailer.AD_STREET)-STAGE VARIABLE - STREET.
.
.
aaIfNullReturn(hash_Biseps_Retailer.AD_CONTACTSURNAME,FIRSTNAME,FIRSTNAME:",":hash_Biseps_Retailer.AD_CONTACTSURNAME)->STAGE VARIABLE-SURNAME
And finally in transformer I used the condition like below:
if Biseps_Retailer.BISEPS_RETAILER = -1 then @NULL
else
if Biseps_Retailer.BISEPS_RETAILER > 0 then
SURNAME
else @NULL
Thanks a lot, Chulett for your guidance.
Sujatha K