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,

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...

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.

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

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...

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,