DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Thu Feb 11, 2016 6:20 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Windows
I am creating a job to clean addresses and then conform to USPS Postal Standard. Can I use AVI to cleanse and correct data to Postal Standard?

Also - can we get output fields from AVI with time zone (i.e. Eastern, Central, UTC/GMT) information?

Thanks in advance.
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54398
Location: Sydney, Australia
Points: 295054

Post Posted: Thu Feb 11, 2016 3:25 pm Reply with quote    Back to top    

I don't believe so.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
rjdickson
Participant



Joined: 16 Jun 2003
Posts: 378
Location: Chicago, USA
Points: 2531

Post Posted: Fri Feb 12, 2016 1:14 pm Reply with quote    Back to top    

AVI does apply local postal standards (i.e. German addresses formatted the way Deutsche Post wants, US addresses formatted the way USPS wants it, etc).

And Ray is correct - AVI itself does not output any time information. However, you could get the current time and add that as a column in the output yourself using a QualityStage Transformer, Column Generator, or various other ways.

_________________
Regards,
Robert
Rate this response:  
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Fri Feb 12, 2016 2:03 pm Reply with quote    Back to top    

Thanks Ray and RJDickson!! I would have another question.....do you know if there in any pattern action code I can add to USNAME.PAT file to get the string after the * (Asterisk)? For Instance....

*** USE ABC # 123 *** NEW YORK

I like to store the information after last asterisk (i.e. NEW YORK) in AdditionalName_USNAME field. What code should I use in the Pattern Action file?

Thanks in advance!!
Rate this response:  
Not yet rated
rjdickson
Participant



Joined: 16 Jun 2003
Posts: 378
Location: Chicago, USA
Points: 2531

Post Posted: Fri Feb 12, 2016 2:34 pm Reply with quote    Back to top    

Well, you already have * in the striplist and striplist, so asterisks are not ever seen. You would have to remove the * from the striplist in order to see them. IF you decide to do this, please perform a full regression test to make sure you have not negatively impacted your ruleset.

Having said that, assuming you DO remove asterisk in the 'striplist', you can do:

Code:
#\* | **             ; The last asterisk, followed by anything, eg: *** USE ABC # 123 *** NEW YORK
COPY_S [1] {OutData} ;NOTE: Normally this would be [2], but this is a workaround because of the #\*
RETYPE [1] 0         ;NOTE: Normally this would be [2], but this is a workaround because of the #\*


The tricks are:
1) '#', at the beginning means to 'scan from right'
2) You need to 'escape' the special character (the *)
3) Use a workaround to treat your data as [1] instead of [2]

_________________
Regards,
Robert
Rate this response:  
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Fri Feb 12, 2016 10:23 pm Reply with quote    Back to top    

Appreciate it Robert. Thank you so much!!
Rate this response:  
Not yet rated
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Thu Feb 18, 2016 10:20 pm Reply with quote    Back to top    

Thanks for your help. I need little more help from you.....

#\* | ** ; The last asterisk, followed by anything, eg: *** USE ABC # 123 *** NEW YORK
COPY_S [1] {OutData} ;NOTE: Normally this would be [2], but this is a workaround because of the #\*
RETYPE [1] 0 ;NOTE: Normally this would be [2], but this is a workaround because of the #\*

How can I produce STANDARDIZED version of OutData?

For example, If I have *** USE ABC # 123 *** COTON & WELSH , INC .

I should see COTON AND WELSH as OutData.
Rate this response:  
Not yet rated
rjdickson
Participant



Joined: 16 Jun 2003
Posts: 378
Location: Chicago, USA
Points: 2531

Post Posted: Fri Feb 19, 2016 7:07 am Reply with quote    Back to top    

What do you see now?

_________________
Regards,
Robert
Rate this response:  
Not yet rated
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Tue Mar 01, 2016 9:24 pm Reply with quote    Back to top    

I see COTON & WELSH , INC . as OutData.

I anticipate to see COTON AND WELSH as OutData
and, INC as NameSuffix_USNAME

Basically, whatever we are putting in OutData should be Standardized again. Therefore, COTON & WELSH , INC needs to be standardized by USNAME rule set after the ** are removed by the code you already suggested.

Thanks in advance Smile
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54398
Location: Sydney, Australia
Points: 295054

Post Posted: Tue Mar 01, 2016 10:22 pm Reply with quote    Back to top    

", INC" will never be standardized by USNAME into the main name field. You can use a downstream Transformer stage to append the Name suffix if that's what you require.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
rjdickson
Participant



Joined: 16 Jun 2003
Posts: 378
Location: Chicago, USA
Points: 2531

Post Posted: Wed Mar 02, 2016 12:53 am Reply with quote    Back to top    

What you are asking for is not recommended. What if you have 'abc supply Corp ** cotton & Welch, Inc' as your data? You would overwrite Corp with Inc.

Is it really two different companies, or one? You could send the additional name into another standardization stage if you wanted...

_________________
Regards,
Robert
Rate this response:  
Not yet rated
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Thu Mar 03, 2016 10:00 am Reply with quote    Back to top    

Basically my scenario is like......

There are either "DO NOT USE" or "ONLY USE" before the **
I am using your code and successfully able to store whatever I have after ** into OutData field or PrimaryName_USNAME in my mapping.

Therefore, **Do Not Use** cotton & Welch, Inc will give me
Cotton & Welch, Inc into Outdata or PrimaryName_USNAME in my mapping.

What I am trying to achieve is below....

The OutData/PrimaryName_USNAME in my mapping should also be standardized like below....

COTTON AND WELCH ---> PrimaryName_USNAME
INC ---> NameSuffix_USNAME

Thank you both Ray and RJDickson in advance !!
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54398
Location: Sydney, Australia
Points: 295054

Post Posted: Thu Mar 03, 2016 5:11 pm Reply with quote    Back to top    

Perhaps this would be more easily solved using two Input Text Overrides to RETYPE the "offending" items to the NULL class (0), so that the rule set can properly process the remainder.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
mahmudul
Participant



Joined: 07 May 2010
Posts: 15

Points: 142

Post Posted: Tue Mar 08, 2016 10:31 am Reply with quote    Back to top    

That works. Thanks!!
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours