Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Proper function needed...
Go
New
Search
Notify
Tools
Reply
  
Proper function needed...
 Login/Join
 
Member
posted
I have a question that I’m hoping someone can help with. I’ve tried various functions but have yet found what I need to accomplish this.

Below is a sample street address as it is stored in our system.

123 MAIN ST APT 12

What I need to do is keep ‘123 MAIN ST’ in one field and create a second field with just ’12.’ I believe the define would start with:

ADDDRESS2/A5 = IF STREET_ADDR_1 CONTAINS ‘APT’ OR ‘UNIT’ OR ‘FL’ THEN….

I just don’t know how to keep everything before ‘Apt’ in field 1 and keep everything after ‘Apt’ in field 2. I tried TRIM and a few other functions but have not been successful.

I would truly appreciate any suggestions or help with this.


7.1.1
 
Posts: 3 | Registered: April 04, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Look up CHKFMT and see if that can help.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Jackie,

You can use POSIT to find the position of APT in your string then use SUBSTR to extract the pieces. Here is an example:

DEFINE FILE CAR
ADDRESS/A25='123 MAIN ST APT 12';
APTLOC/I2=POSIT(ADDRESS,25,'APT',3,'I2');
ADDR1/A25=SUBSTR(25,ADDRESS,1,APTLOC-1,APTLOC,'A25');
APTNO/A5=SUBSTR(25,ADDRESS,APTLOC+3,25,25-APTLOC-3,'A5');
END
TABLE FILE CAR
PRINT COUNTRY ADDRESS APTLOC ADDR1 APTNO
IF RECORDLIMIT EQ 1
END  

Notice the liberal use of arithmetic when calculating start, end, and length parameters.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Ginny that looks good.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Jackie,

One more thing, if POSIT can't find the string APT or whatever you are looking for, then the APTLOC field would be zero. You can test on this before you do the SUBSTR. For instance,

FIELD/Ann=IF APTLOC EQ 0 THEN ' ' ELSE SUBSTR...


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Give this a try:

DEFINE FILE CAR
STREET_ADDR_1/A100 = 
  IF COUNTRY EQ 'ENGLAND' THEN '123 MAIN ST UNIT 169' ELSE
  IF COUNTRY EQ 'ITALY'   THEN '3478 VIA APPIA FL 10' ELSE
  IF COUNTRY EQ 'FRANCE'  THEN '789 RUE ST. PAUL APT 1783' ELSE '392 HURON STREET';

NBR_LOC/I2 =
  IF STREET_ADDR_1 CONTAINS 'APT'  THEN POSIT(STREET_ADDR_1, 100, 'APT' , 3, 'I2') ELSE
  IF STREET_ADDR_1 CONTAINS 'UNIT' THEN POSIT(STREET_ADDR_1, 100, 'UNIT', 4, 'I2') ELSE
  IF STREET_ADDR_1 CONTAINS 'FL'   THEN POSIT(STREET_ADDR_1, 100, 'FL'  , 2, 'I2') ELSE 0;

STR_LEN/I2 = 
  IF STREET_ADDR_1 CONTAINS 'APT'  THEN 3 ELSE
  IF STREET_ADDR_1 CONTAINS 'UNIT' THEN 5 ELSE
  IF STREET_ADDR_1 CONTAINS 'FL'   THEN 2 ELSE 0;

APT_NBR/A6 = IF NBR_LOC GT 0 THEN SUBSTR(100, STREET_ADDR_1, NBR_LOC+STR_LEN, NBR_LOC+STR_LEN+6, 6, 'A6') ELSE '';
END
TABLE FILE CAR
PRINT
STREET_ADDR_1
NBR_LOC
APT_NBR
COUNTRY
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
As an alternative to Ginny's excellent suggestion, try using GETTOK like this -
APP FI ADDRESS DISK ADDRESS.MAS (LRECL 80
-RUN
-WRITE ADDRESS
-WRITE ADDRESS FILE=ADDRESS,SUFFIX=FOC
-WRITE ADDRESS SEGNAME=SEG1
-WRITE ADDRESS FIELD=ADDRESS_DATA,   ,A100   ,A100   , $
-RUN
CREATE FILE ADDRESS
MODIFY FILE ADDRESS
FREEFORM ADDRESS_DATA
DATA
123 MAIN ST APT 12,$
345 HIGH ST FL 123,$
678 EAST ST UNIT 19,$
1924 FLAPTUNIT SECTION 21,$
END
-RUN
DEFINE FILE ADDRESS
  NEW_ADDR/A100 = IF ADDRESS CONTAINS ' APT ' THEN STRREP(100, ADDRESS, 5, ' APT ', 1, '~', 100, NEW_ADDR)
             ELSE IF ADDRESS CONTAINS ' FL '  THEN STRREP(100, ADDRESS, 4, ' FL ', 1, '~', 100, NEW_ADDR)
             ELSE IF ADDRESS CONTAINS ' UNIT' THEN STRREP(100, ADDRESS, 6, ' UNIT ', 1, '~', 100, NEW_ADDR) 
             ELSE ADDRESS ;
  ADDR_PT1/A100 = GETTOK(NEW_ADDR, 100, 1, '~', 100, ADDR_PT1);
  ADDR_PT2/A100 = GETTOK(NEW_ADDR, 100, -1, '~', 100, ADDR_PT2);
END
TABLE FILE ADDRESS
PRINT ADDR_PT1
      ADDR_PT2
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5692 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
My suggestion would be the same as Tony's. This assumes that the apt/fl/unit number would be the last item in the field, but that would be a pretty safe assumption. Then, as Tony did, just parse the field into 1-everything BEFORE the last space(or ~ in Tony's case) and 2-everything AFTER the last space. If you're leaving as spaces, you would need to use TRIM to remove trailing spaces and format as AnV.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thank you ALL!!! I was able to use a combination of these suggestions to make this work. I'm still fairly new to Focus and so I'm not certain what some of this coding did... but it worked!!!


7.1.1
 
Posts: 3 | Registered: April 04, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Jackie

I'm glad your problem is solved, but it would be better if you try to find out how it is solved.
Tomorrow you will get an other question.

Try to lookup the defines in your manuals and to understand what is done.


DEFINE FILE CAR 
ADDRESS/A25='123 MAIN ST APT 12'; 
-* find in the string the place where the substring starts
APTLOC/I2=POSIT(ADDRESS,25,'APT',3,'I2');
-*select the substring that holds the first part.
ADDR1/A25=SUBSTR(25,ADDRESS,1,APTLOC-1,APTLOC,'A25');
-*select the substring that holds the second part.
APTNO/A5=SUBSTR(25,ADDRESS,APTLOC+3,25,25-APTLOC-3,'A5');
END
TABLE FILE CAR
PRINT COUNTRY ADDRESS APTLOC ADDR1 APTNO 
IF RECORDLIMIT EQ 1
END


What Tony and Francis did is in fact the same.

But they first created records because they did not have your dataset. (the write command Tony used did this)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
... you'll also notice that I split the text using ' APT ' etc. e.g. a space either side of the target text. This is to ensure that incorrect parsing of the text is avoided - as in the fourth example data item.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5692 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Member
posted Hide Post
To All -

I am so grateful for all your help! I absolutely will be researching each of the functions so I understand them better and can use them on future reports. This is a great forum and I truly appreciate the help you all offered so quickly. Smiler


7.1.1
 
Posts: 3 | Registered: April 04, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Proper function needed...

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.