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:

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
END
TABLE FILE CAR
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
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
Give this a try:

```DEFINE FILE CAR
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 '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
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
-RUN
DATA
123 MAIN ST APT 12,\$
345 HIGH ST FL 123,\$
678 EAST ST UNIT 19,\$
1924 FLAPTUNIT SECTION 21,\$
END
-RUN
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)
END
END```

T

 In FOCUSsince 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
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
-* find in the string the place where the substring starts
-*select the substring that holds the first part.
-*select the substring that holds the second part.
END
TABLE FILE CAR
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,IE7test: 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 FOCUSsince 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
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.

7.1.1