• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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))
0
mahalakshmi_s
Asked:
mahalakshmi_s
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
diasroshanCommented:
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now