concatenation of null values

[sujaoschin]

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

[chulett]

Wrap each possibly null field in the NullToEmpty() transform.

[sujaoschin]

If I know definitely which field is coming null, I can give Nulltoempty(). Please can you explain further with my example itself.

[chulett]

Just use it directly in the concatenation for the fields. Worst case:

Code: Select all

NullToEmpty(company):NullToEmpty(suburb):NullToEmpty(postcode):NullToEmpty(firstname):NullToEmpty(surname):NullToEmpty(telephone)

Obviously not syntactically correct without the link name.

[sujaoschin]

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?

[chulett]

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:

Code: Select all

: NullToEmpty(hash_Biseps_Retailer.AD_CONTACTFIRSTNAME) : "," : 

Something like this instead:

Code: Select all

: (If IsNull(hash_Biseps_Retailer.AD_CONTACTFIRSTNAME) Then "" Else hash_Biseps_Retailer.AD_CONTACTFIRSTNAME:",") :

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.

[sujaoschin]

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.

[sujaoschin]

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.