compute field formatting for mailing address in DW

I want mailing address format like:

Owner name
Addl_owner name
secondary owner name
addl_addr

If anyone is missing, I should not get any blank lines inbetween.can anybody say whats wrong below? Am getting one empty line if I dont have addl_owner name . Others r working fine.


If ( Len(Trim(owner_first_name))  = 0 Or IsNull(owner_first_name )  , '', Trim(owner_first_name)  ) +
If ( Len(Trim( owner_initial_name ))  = 0 Or IsNull( owner_initial_name )  , '', ' ' + Trim(owner_initial_name) + '.' ) +
If(IsNull( owner_last_name ) or Len(Trim( owner_last_name )) = 0 ,'', ' ' + Trim(owner_last_name)) +
If(IsNull(  owner_name_suffix ) or Len(Trim(  owner_name_suffix )) = 0 ,'', ' ' +Trim(owner_name_suffix) + '.') +
'~r~n' +
If ( Len(Trim(addl_owner_first_name))  = 0 Or IsNull(addl_owner_first_name )  , '',Trim(addl_owner_first_name) ) +
If ( Len(Trim(addl_owner_initial_name ))  = 0 Or IsNull(addl_owner_initial_name )  , '', ' ' + Trim(addl_owner_initial_name) + '.') +
If(IsNull(addl_owner_last_name ) or Len(Trim(addl_owner_last_name )) = 0 ,'', ' ' + Trim(addl_owner_last_name)) +
If(IsNull(addl_owner_name_suffix ) or Len(Trim(addl_owner_name_suffix )) = 0 ,'', ' ' +  Trim(addl_owner_name_suffix) + '.') + '~r~n' +
If(isNull(secondary_name ) Or len(secondary_name) = 0, '',trim(secondary_name) +'~r~n') +
if(isNull(owner_addl_addr) Or len(owner_addl_addr)=0,'',trim(owner_addl_addr))
mahalakshmi_sAsked:
Who is Participating?
 
diasroshanConnect With a Mentor Commented:
hi,

try the following in ur computed field.... its just an enhancement of what u had posted in ur question...

If ( Len(Trim(owner_first_name))  = 0 Or IsNull(owner_first_name )  , '', Trim(owner_first_name)  ) +
If ( Len(Trim( owner_initial_name ))  = 0 Or IsNull( owner_initial_name )  , '', ' ' + Trim(owner_initial_name) + '.' ) +
If(IsNull( owner_last_name ) or Len(Trim( owner_last_name )) = 0 ,'', ' ' + Trim(owner_last_name)) +
If(IsNull(  owner_name_suffix ) or Len(Trim(  owner_name_suffix )) = 0 ,'', ' ' +Trim(owner_name_suffix) + '.') +
If ( Len(Trim(addl_owner_first_name))  = 0 Or IsNull(addl_owner_first_name )  , '','~r~n' + Trim(addl_owner_first_name) ) +
If ( Len(Trim(addl_owner_initial_name ))  = 0 Or IsNull(addl_owner_initial_name )  , '', ' ' + Trim(addl_owner_initial_name) + '.') +
If(IsNull(addl_owner_last_name ) or Len(Trim(addl_owner_last_name )) = 0 ,'', ' ' + Trim(addl_owner_last_name)) +
If(IsNull(addl_owner_name_suffix ) or Len(Trim(addl_owner_name_suffix )) = 0 ,'', ' ' +  Trim(addl_owner_name_suffix) + '.') +
If(isNull(secondary_name ) Or len(secondary_name) = 0, '','~r~n' + trim(secondary_name) ) +
if(isNull(owner_addl_addr) Or len(owner_addl_addr)=0,'','~r~n' + trim(owner_addl_addr))

Hope it helps...

Cheers,
Rosh
0
 
sajuksCommented:
change this
If(IsNull(  owner_name_suffix ) or Len(Trim(  owner_name_suffix )) = 0 ,'', ' ' +Trim(owner_name_suffix) + '.') +
'~r~n' +
to
If(IsNull(  owner_name_suffix  ) or Len(Trim(  owner_name_suffix )) = 0 ,'',  ' ' +Trim(owner_name_suffix ) + '.'+ '~r~n' ) +

u might've to do the same for ddl_owner_name_suffix

basically u've to put the break condition inside the if condition for null ~r~n which u've done properly over here
If(isNull(secondary_name ) Or len(secondary_name) = 0, '',trim(secondary_name) +'~r~n') +
0
 
gajender_99Commented:
hi try this

string rtn_add


      if trim(Owner name)<>"" then
            
            rtn_add=rtn_add+(trim(Owner name))+space(40 - len(trim(Owner name)))+'~n'
      end if      
 
      if trim(Addl_owner name)<>"" then
            rtn_add=rtn_add+(trim(Addl_owner name)) +space(40 - len(trim(Addl_owner name)))+'~n'
      end if      
        
      if trim(secondary owner name)<>"" then
            rtn_add=rtn_add+(trim(secondary owner name)) +space(40 - len(trim(secondary owner name)))+'~n'
      end if      
        
      if trim(addl_addr)<>"" then
            rtn_add=rtn_add+(trim(addl_addr)) +space(40 - len(trim(addl_addr)))+'~n'
      end if      
            
      
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mahalakshmi_sAuthor Commented:
I was wondering where PB guys are ???

well Sajuks...if I do that , if I have Addl_owner name , then it will print in the same line. Actaully its ~r~n is inside this only one IF condn.

If(IsNull(  owner_name_suffix  ) or Len(Trim(  owner_name_suffix )) = 0 ,'',  ' ' +Trim(owner_name_suffix ) + '.'+ '~r~n' ) +
So am getting owner_name,Addl_owner name  in same line.

In another report I have only 2 flds say
Owner name
Addl_owner name

where I ahve this code n working perfect.

If ( Len(Trim(owner_first_name))  = 0 Or IsNull(owner_first_name )  , '', Trim(owner_first_name)  ) +
If ( Len(Trim( owner_initial_name ))  = 0 Or IsNull( owner_initial_name )  , '', ' ' + Trim(owner_initial_name) + '.' ) +
If(IsNull( owner_last_name ) or Len(Trim( owner_last_name )) = 0 ,'', ' ' + Trim(owner_last_name)) +
If(IsNull(  owner_name_suffix ) or Len(Trim(  owner_name_suffix )) = 0 ,'', ' ' +Trim(owner_name_suffix) + '.') +
'~r~n' +
If ( Len(Trim(addl_owner_first_name))  = 0 Or IsNull(addl_owner_first_name )  , '',Trim(addl_owner_first_name) ) +
If ( Len(Trim(addl_owner_initial_name ))  = 0 Or IsNull(addl_owner_initial_name )  , '', ' ' + Trim(addl_owner_initial_name)

+ '.' ) +
If(IsNull(addl_owner_last_name ) or Len(Trim(addl_owner_last_name )) = 0 ,'', ' ' + Trim(addl_owner_last_name)) +
If(IsNull(addl_owner_name_suffix ) or Len(Trim(addl_owner_name_suffix )) = 0 ,'', ' ' +  Trim(addl_owner_name_suffix) + '. ')

Pls help,
Tq
Maha


0
 
mahalakshmi_sAuthor Commented:
gajender_99
where can i code the above ?
 I have 4 parts for owner_name n addl_owner_name.
u mena to say I shd write in FormulaWindow (Modify Expression Window in PB)

Kindly help
Tq
M
0
 
mahalakshmi_sAuthor Commented:
is there anyway i can cocatenate the four parts of name n put as one name so that I can use as in

If(isNull(secondary_name ) Or len(secondary_name) = 0, '',trim(secondary_name) +'~r~n') +

I know that ~r~n shd be inside one IF condn but am going mad...:-(((((

As this report sending to users,we are particular abt displaying the name with initial/suffix etc.

Tq
M
0
 
mahalakshmi_sAuthor Commented:
I have one comp fld n pasted the working code.If owner_name , addl_owner_name are present , its printing n if one is missing.It does not leave empty line.
If i place one more CF silimarly to
secondary owner name
addl_addr

and any one is missing am getting empty line.These secondary owner name
addl_addr cols are directly taken from db.hence no formatting needed. I tried putting in diff comp fld n used SLIDE UP/ALL ABOVE/DIRECTLY ABOVE etc , but all in vain :-(
0
 
mahalakshmi_sAuthor Commented:
ooooooooooooooppssss....seems PB experts r busy ?
0
 
Sys_ProgCommented:
mahalakshmi_s,

can u elaborate a little bit
1. Where r using this
2. R all of thm computed columns - one for each
3. These expressions reside in computed columns for each ??

amit

0
 
mahalakshmi_sAuthor Commented:
1)I want mailing label to printed n sent to customers.I use datawindow in Pb10.
2) 1 comp fld for formatting these 4 lines
3) this one big expr given above reside in 1 cf only

owner_name (4 parts) n addl_owner_name (4 parts) formatting is must as we print n send letters to customers using USPS.secondary_name,owner_addl_addr are just starightway database columns n no formatting here.

But amit,u can suggest me some other way also.
I tried to have one CF for each line but whenever there is no data, empty line was printing which was wrong.
I figured that '~r~n' must be inside some looping condn...

Thanks
Maha


Rosh: will try urs n let u know. Thanks :-)
0
 
mahalakshmi_sAuthor Commented:
Rosh, that was still leaving empty lines :-( sorry.

0
 
diasroshanCommented:
hi,

im surprised...

if u see the syntax i've posted i am applying a carraige return(~r~n) only if the first column of the new line is available...

can u paste the exact output ur gettin....

Cheers,
Rosh
0
 
diasroshanCommented:
Hi,

<waiting>

Cheers,
Rosh
0
 
mahalakshmi_sAuthor Commented:
Rosh,
I adopted another way n fixed the problem. Finally I had 6 lines and had 3 comp flds. I placed carriage return in columns where there is single value like POBox/Street name and NOT in trouble some name fld s where I have to concatenate 4 cols n make one line !!!!!!!!!!!
Anyways I used ur input too.Many thanks.
More sooooooooooner,
Maha
0
 
diasroshanCommented:
Thanx for the points and grade !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.