Focal Point
Proper function needed...

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6981026972

April 08, 2008, 10:01 AM
jackiekim
Proper function needed...
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
April 08, 2008, 10:05 AM
Prarie
Look up CHKFMT and see if that can help.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
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
Ginny that looks good.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
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
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
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
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
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
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

... 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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
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