Hi All,

I have 2 Mainframe PS files (in focus) with following layouts.

Driving file:
WO_NUM CALL_DATE
ABC1234567 05/01/2012
BCD2345678 15/02/2012
CDE3456789 20/03/2012
DEF4567890 02/04/2012

2nd file with Fee:
EFF_START_DT EFF_END_DT FEE_AMT ON_OFF
01/01/2012 31/01/2012 20.00 Y
01/02/2012 29/02/2012 25.00 Y
01/03/2012 31/03/2012 30.00 Y
01/04/2012 30/04/2012 20.00 N

I need to populate the FEE_AMT for each WO_NUM by finding out CALL_DATE in between EFF_START_DT & EFF_END_DT.

Expected output:
WO_NUM CALL_DATE FEE_AMT
ABC1234567 05/01/2012 20.00
BCD2345678 15/02/2012 25.00
CDE3456789 20/03/2012 30.00
DEF4567890 02/04/2012 0.00

I will not be able to use JOIN since the date values in both the files will not be same.

Please suggest what is the best way to do this in Focus

Rajesh

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

Of course I don't know much about your data. Nonetheless, here is something you could start with.
Master Files:

  
FILENAME=rajesh1, SUFFIX=FIX     , $
  SEGMENT=RAJESH1, SEGTYPE=S0, $
    FIELDNAME=WO_NUM, ALIAS=WO_NUM, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=CALL_DATE, ALIAS=CALL_DATE, USAGE=DMYY, ACTUAL=A10, $

  
FILENAME=rajesh2, SUFFIX=FIX     , $
  SEGMENT=RAJESH2, SEGTYPE=S0, $
    FIELDNAME=EFF_START_DT, ALIAS=EFF_START_DT, USAGE=DMYY, ACTUAL=A10, $
    FIELDNAME=EFF_END_DT, ALIAS=EFF_END_DT, USAGE=DMYY, ACTUAL=A10, $
    FIELDNAME=FEE_AMT, ALIAS=FEE_AMT, USAGE=D7.2, ACTUAL=A5, $
    FIELDNAME=ON_OFF, ALIAS=ON_OFF, USAGE=A1, ACTUAL=A1, $

Data Files:
Rajesh1

ABC123456705/01/2012
BCD234567815/02/2012
CDE345678920/03/2012
DEF456789002/04/2012
GHJ456789007/02/2012
DEF987654312/01/2012
DEF123456712/05/2012

Rajesh2

  
01/01/201231/01/201220.00Y
01/02/201229/02/201225.00Y
01/03/201231/03/201230.00Y
01/04/201230/04/201220.00N

Procedure:

  
-* File RAJESHM.fex
FILEDEF RAJESH1 DISK focalpoint/Rajesh1.txt
FILEDEF RAJESH2 DISK focalpoint/Rajesh2.txt
DEFINE FILE RAJESH1
CALL_DATE01/DMYY=DATEMOV(CALL_DATE, 'BOM');
END
MATCH FILE RAJESH1
SUM CALL_DATE 
BY CALL_DATE01 AS BOM
BY WO_NUM
RUN
FILE RAJESH2
SUM
EFF_END_DT FEE_AMT ON_OFF
BY EFF_START_DT AS BOM
AFTER MATCH HOLD OLD-OR-NEW
END
DEFINE FILE HOLD
NEW_FEE/D7.2=IF (CALL_DATE GE BOM) AND (CALL_DATE LE EFF_END_DT) AND (ON_OFF EQ 'Y') THEN FEE_AMT ELSE 0;
END 
TABLE FILE HOLD 
PRINT NEW_FEE
BY WO_NUM BY CALL_DATE
END

I hope this helps.

You can still use JOIN even without common fields. Look up CONDITIONAL JOIN or WHERE-BASED JOIN in the help files.

In your case (from what I gather in your sample format), you can join CALL_DATE with EFF_START_DT (or any field in the 2nd file for that matter) and add the WHERE clause.

Okay, it may look something like this:

JOIN FILE DRIVING_FILE AT CALL_DATE TAG F1
TO ALL FILE SECOND_FILE AT EFF_START_DT TAG F2
AS J0
WHERE (F1.CALL_DATE GE F2.EFF_START_DT) AND (F1.CALL_DATE LE F2.EFF_END_DT);
END

Hope this helps.

However you accomplish the join, make sure your report will not double-count the fees when there are multiple service-date instances within one fee date-range.

- - -

So (as a note to Anatess' post) it's notionally a one-to-many join in the opposite direction: One instance of "2nd file with Fee" to "many" (zero or more) instances of "Driving file".

Thank you everyone for the reply.

I was able to get the output with the join comand as mentioned by Anatess.

My fee file effective start date may not have beginning of the month on all the records. Sorry for not making this clear in my question.

I will be having Date validation to prior to this make sure that there will not be any duplicate ranges.

Thanks again.