Reclassifying Data Based on a Comparison

I have 2 groups of data, the first with companies having public bonds and the second with companies having private bonds.

If a company is listed in both public and private bond groups I need to reclassify the private bonds as 'public' or in other words show the company only as a public company.

What is the best way to compare my 2 groups to identify companies having both public and private bonds and then dumping these companies into the public bucket?

I've attempted to do this, but I lose either the public or private 'bucket'. I'd appreciate any help with this, thank you!

vickie

Posts: 37 | Location: Springfield, MA | Registered: December 03, 2004

Tony A

Expert

posted

Hide Post

Hmmmm ... "best" way you say? Well that depends upon your source data of course and the RDBMS in which it is held. If it is SQL based then I would be inclined to use SQL passthru for efficiency.

However, if you want a pure FOCUS solution (not necessarily the "best") then try this code

TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS PUBLIC FORMAT FOCUS INDEX COUNTRY
END

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS PRIVATE FORMAT FOCUS INDEX COUNTRY
END

DEFINE FILE PUBLIC
  BONDS/A9 = 'PUBLIC';
END
DEFINE FILE PRIVATE
  BONDS/A9 = 'PRIVATE';
END

MATCH FILE PUBLIC
SUM BONDS 
BY COUNTRY
RUN
FILE PRIVATE
SUM BONDS 
BY COUNTRY
AFTER MATCH HOLD AS BONDS OLD-OR-NEW
END
-RUN

DEFINE FILE BONDS
  TYPE/A9 = IF E02 NE '' THEN E02 ELSE E03;
END

TABLE FILE BONDS
PRINT TYPE
BY COUNTRY
END

The first two table requests are purely to provide some basic data. The important item is the MATCH processing. This provides a method of combining the output similar to using a UNION ALL.

Anyway, have a look at the code and see if it meets your needs.

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: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

Vickie

Silver Member

posted

Hide Post

Thanks, Tony. I'll try this. Actually do want pure FOCUS, 'best' or not,

vickie

Posts: 37 | Location: Springfield, MA | Registered: December 03, 2004

Ignored post by Vickie posted

Show Post

GCohen

Platinum Member

posted

Hide Post

It would be more direct to just MATCH the names and find those that are both Public and Private.
MATCH FILE data
BY COMPANY
IF BOND EQ 'PUBLIC'
RUN
FILE data
BY COMPANY
IF BOND EQ 'PRIVATE'
ON TABLE HOLD AS OLD-AND-NEW
END
TABLE FILE HOLD
PRINT COMPANY <-these are the ones with both
END

Release 7.6.9
Windows
HTML

Posts: 226 | Registered: June 08, 2003

Ignored post by GCohen posted

Show Post

Vickie

Silver Member

posted

Hide Post

That would do it. Then I could add back the companies that are only public for a complete listing of all companies with public or public and private bonds.

Thank you! vickie

Posts: 37 | Location: Springfield, MA | Registered: December 03, 2004

Ignored post by Vickie posted

Show Post

j.gross

Virtuoso

posted

Hide Post

Something like this would classify all issuers without requireing MATCH:

DEFINE FILE BONDDATA
PRIVATE/I5= BOND_TYPE EQ 'PRIVATE';
PUBLIC /I5= BOND_TYPE EQ 'PUBLIC';
END
TABLE FILE BONDDATA
SUM PRIVATE NOPRINT
PUBLIC NOPRINT
COMPUTE COMP_TYPE/A8=
IF PRIVATE GT 0 AND PUBLIC EQ 0 THEN 'PRIVATE'
ELSE 'PUBLIC ';
BY COMPANY
END

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Reclassifying Data Based on a Comparison

Focal Point | Privacy Statement | Terms of Service