Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Match non matchable tables [SOLVED]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Match non matchable tables [SOLVED]
 Login/Join
 
Virtuoso
posted
I have two tables

STOCKPRICES and CLIENTPOSITIONS

The first holds the price that is valuable from a certain date. The secont holds the position per client as per the first day of the month.

I want to calculate the value of the portfolio per client per month.

If the STOCKPRICES would have a price per stoch per the first of the month it would not have been an ussue here.....

Example data

STOCKPRICES:
SECID FROMDATE PRICE
12345 20071112 17.50
12345 20080213 16.00
12345 20080516 12.00
12345 20080722 13.33


CLIENTPOSITIONS
CLTNR  SECID DATE     NUMBER
0001   12345 20071201   250
0001   12345 20080101   275
0001   12345 20080201   245
...
0001   12345 20080401   230
...
0001   12345 20080701   300


The end result would give something like this

CLTNR  SECID DATE     NUMBER PRICE
0001   12345 20071201   250   17.50 (price 20071112)
0001   12345 20080101   275   17.50 (price 20071112)
0001   12345 20080201   245   17.50 (price 20071112)
0001   12345 20080401   230   16.00 (price 20080213)
0001   12345 20080701   300   12.00 (price 20080516)


The next price of 20080722 is valuable per the 1st of August

Any idea?

This message has been edited. Last edited by: FrankDutch,




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, 2006Report This Post
Virtuoso
posted Hide Post
This little piece of coding came to mind:
FILEDEF MFD DISK mfd1.mas
-RUN
-WRITE MFD FILENAME=MFD1, SUFFIX=FIX, $
-WRITE MFD SEGNAME=MFD1, $
-WRITE MFD FIELDNAME=SECID   , FORMAT=A5   , ACTUAL=A5 , $
-WRITE MFD FIELDNAME=FROMDATE, FORMAT=YYMD , ACTUAL=A8 , $
-WRITE MFD FIELDNAME=PRICE   , FORMAT=D12.2, ACTUAL=A5 , $

FILEDEF MFD1 DISK mfd1.ftm
-RUN
-WRITE MFD1 123452007111217.50
-WRITE MFD1 123452008021316.00
-WRITE MFD1 123452008051612.00
-WRITE MFD1 123452008072213.33

FILEDEF MFD DISK mfd2.mas
-RUN
-WRITE MFD FILENAME=MFD2, SUFFIX=FIX, $
-WRITE MFD SEGNAME=MFD2, $
-WRITE MFD FIELDNAME=CLTNR  , FORMAT=A4   , ACTUAL=A4 , $
-WRITE MFD FIELDNAME=SECID  , FORMAT=A5   , ACTUAL=A5 , $
-WRITE MFD FIELDNAME=DATE   , FORMAT=YYMD , ACTUAL=A8 , $
-WRITE MFD FIELDNAME=NUMBER , FORMAT=I4   , ACTUAL=A3 , $

FILEDEF MFD2 DISK mfd2.ftm
-RUN
-WRITE MFD2 00011234520071201250
-WRITE MFD2 00011234520080101275
-WRITE MFD2 00011234520080201245
-WRITE MFD2 00011234520080401230
-WRITE MFD2 00011234520080701300

TABLE FILE MFD1
PRINT *
COMPUTE BLANK/A1 = '';
COMPUTE TODATE/YYMD = IF LAST FROMDATE EQ 0 THEN &YYMD ELSE LAST FROMDATE;
BY HIGHEST FROMDATE NOPRINT
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT PRICE
BY BLANK
BY SECID
BY FROMDATE
BY TODATE
ON TABLE HOLD AS FT FORMAT FOCUS INDEX BLANK
END

JOIN BLANK WITH CLTNR IN MFD2 TO ALL BLANK IN FT TAG RT AS J

DEFINE FILE MFD2
BLANK/A1 WITH CLTNR = ' ';
END

TABLE FILE MFD2
PRINT PRICE FROMDATE TODATE
BY CLTNR
BY SECID
BY DATE
BY NUMBER
WHERE MFD2.DATE GE RT.FROMDATE AND MFD2.DATE LE RT.TODATE
END

I think it does what you need...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Looks easy..

So the trick is McGuyver and your JOIN on the field BLANK that is defined AFTER the JOIN.

I'll give it a try and let you know if it works.




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, 2006Report This Post
Expert
posted Hide Post
Looks like Gamp replied quicker!

I'd not bother with MATCH but use a "loose" join (as has GAMP) where a secondary field is loosely matched by using less than etc. operators.

As I've got an alternative I'll post it anyway - it only handles the data twice and not three times.

EX -LINES 5 EDAPUT MASTER,STOCKPRICES,CF,MEM,FILENAME=STOCKPRICES, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S1 ,$
  FIELD=SECID,         ,I9    ,I9     ,FIELDTYPE=I, $
  FIELD=FROMDATE,      ,YYMD  ,YYMD   ,$
  FIELD=PRICE,         ,D12.2 ,D12.2  ,$
-RUN

EX -LINES 6 EDAPUT MASTER,CLIENTPOSITIONS,CF,MEM,FILENAME=CLIENTPOSITIONS, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S1 ,$
  FIELD=CLTNR,         ,A4    ,A4     ,$
  FIELD=SECID,         ,I9    ,I9     ,FIELDTYPE=I, $
  FIELD=DATE,          ,YYMD  ,YYMD   ,$
  FIELD=NUMBER,        ,D12   ,D12    ,$
-RUN
CREATE FILE STOCKPRICES
MODIFY FILE STOCKPRICES
FREEFORM SECID FROMDATE PRICE
DATA
12345,20071112,17.50,$
12345,20080213,16.00,$
12345,20080516,12.00,$
12345,20080722,13.33,$
END
-RUN
CREATE FILE CLIENTPOSITIONS
MODIFY FILE CLIENTPOSITIONS
FREEFORM CLTNR SECID DATE NUMBER
DATA
0001,12345,20071201,250,$
0001,12345,20080101,275,$
0001,12345,20080201,245,$
0001,12345,20080401,230,$
0001,12345,20080701,300,$
0002,12345,20071201,250,$
0002,12345,20080201,275,$
0002,12345,20080301,245,$
0002,12345,20080401,230,$
0002,12345,20080501,300,$
END
-RUN
JOIN CLEAR *
JOIN SECID IN CLIENTPOSITIONS TO MULTIPLE SECID IN STOCKPRICES AS J1
-*
TABLE FILE CLIENTPOSITIONS
PRINT DATE NUMBER FROMDATE PRICE
   BY CLTNR
   BY SECID
WHERE FROMDATE LE DATE
ON TABLE HOLD AS FRDUTCH
END
-RUN
TABLE FILE FRDUTCH
PRINT NUMBER PRICE 
   BY CLTNR
   BY SECID
   BY DATE
   BY HIGHEST 1 FROMDATE
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF,SIZE=9, $
ENDSTYLE
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: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
quote:
EX -LINES 5 EDAPUT MASTER,STOCKPRICES,CF,MEM,FILENAME=STOCKPRICES, SUFFIX=FOC,$


You are an Expert.....

It works well, even better than Gamps solution since that gave me a problem with the last line when the last stockprice has a date before the last wanted portfolio.

And this quoted line a very handy to test things like this or create small tables.
Until now I always did that by creating the master as a COM file or FIX.

Thanks...




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, 2006Report This Post
Virtuoso
posted Hide Post
Some remarkes on Gamp's solution:

a. Since TODATE is the start of the next price interval, the WHERE should test for DATE strictly less than TODATE
b. With multiple securities, the selection on the joined view must screen on equality of SECID.
c. BLANK is not needed; you can join directly on SECID (which will address [b] as well).
d. The two holds can be collapsed into one, as below. (I used a two-segment hold, so the JOIN is 1-1 on the parent segment)

TABLE FILE mfd1
  COUNT ENTRIES  NOPRINT
    BY SECID
  PRINT PRICE
        FROMDATE NOPRINT
        SECID    NOPRINT
  COMPUTE TODATE/YYMD =
      IF (SECID NE LAST SECID) THEN '&YYMD' ELSE LAST FROMDATE;
    BY SECID
    BY HIGHEST FROMDATE
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FT FORMAT FOCUS INDEX SECID
END
-RUN
JOIN SECID IN mfd2 TO UNIQUE SECID IN FT TAG RT AS J
END
TABLE FILE mfd2
PRINT NUMBER PRICE FROMDATE TODATE
BY CLTNR
BY SECID
BY DATE
WHERE DATE GE RT.FROMDATE AND DATE LT RT.TODATE   ;
END


N.B. In the final JOIN & TABLE, it would be appropriate to place the WHERE in the JOIN (what IBI calls a "conditional join"). But I couldn't get that to work.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Match non matchable tables [SOLVED]

Copyright © 1996-2020 Information Builders