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.
-* 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.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
February 17, 2012, 09:21 AM
Anatess
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.
WF 8.1.05 Windows
February 17, 2012, 12:01 PM
j.gross
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".
February 22, 2012, 12:15 PM
RajeshP
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.