Is there any way using Match File to get the desired output from the two files listed below?

File 1
ID R_date
1 1/15/2008
1 2/17/2009
2 4/19/2012

File 2
ID A_date
1 11/19/2006
1 1/12/2012
1 10/16/2005
2 4/19/2003
2 8/17/2001


Desired Output
ID R_date A_date
1 1/15/2008 11/19/2006
1 1/15/2008 1/12/2012
1 1/15/2008 10/16/2005
1 2/17/2009 11/19/2006
1 2/17/2009 1/12/2012
1 2/17/2009 10/16/2005
2 4/19/2012 4/19/2003
2 4/19/2012 8/17/2001

This message has been edited. Last edited by: Mark Allen Harris,

Did you check out the MATCH FILE Command? What do you think of the AFTER MATCH OLD-OR-NEW? That specifies that all records from both the old data source and the new data source appear in the HOLD file. This is the default if the AFTER MATCH line is omitted.

Is that your desired result?

I tried OLD-OR-NEW. It gives me all the records in both files, but not all the possible combinations.

A conditional JOIN may work:

FILEDEF FILE1 DISK FILE1.FTM
-RUN
-WRITE FILE1 11/15/2008
-WRITE FILE1 12/17/2009
-WRITE FILE1 24/19/2012
-*
FILEDEF FILE1MAS DISK FILE1.MAS
-RUN
-WRITE FILE1MAS FILENAME=FILE1,$
-WRITE FILE1MAS  SEGNAME=ONE,SEGTYPE=S0,$
-WRITE FILE1MAS   FIELDNAME=ID,,FORMAT=I5,ACTUAL=A1,$
-WRITE FILE1MAS   FIELDNAME=R_DATE,,FORMAT=MDYY,ACTUAL=A10,$
-*
FILEDEF FILE2 DISK FILE2.FTM
-RUN
-WRITE FILE2 111/19/2006
-WRITE FILE2 11/12/2012
-WRITE FILE2 110/16/2005
-WRITE FILE2 24/19/2003
-WRITE FILE2 28/17/2001
-*
FILEDEF FILE2MAS DISK FILE2.MAS
-RUN
-WRITE FILE2MAS FILENAME=FILE2,$
-WRITE FILE2MAS  SEGNAME=ONE,SEGTYPE=S0,$
-WRITE FILE2MAS   FIELDNAME=ID,,FORMAT=I5,ACTUAL=A1,$
-WRITE FILE2MAS   FIELDNAME=A_DATE,,FORMAT=MDYY,ACTUAL=A10,$
-RUN
-*
JOIN FILE FILE1 AT ID TO MULTIPLE FILE FILE2 AT ID AS J1
     WHERE FILE1.ID EQ FILE2.ID ;
END
-*
TABLE FILE FILE1
 PRINT *
END

Otherwise, you could create an indexed file from FILE2 and use a standard JOIN:

TABLE FILE FILE2
 BY ID
 BY A_DATE
 ON TABLE HOLD AS FILE2HLD FORMAT FOCUS INDEX ID
END
-*
JOIN CLEAR *
JOIN ID IN FILE1 TO ALL ID IN FILE2HLD AS J1
-*
TABLE FILE FILE1
 PRINT R_DATE A_DATE
 BY ID
 ON TABLE SET BYDISPLAY ON
END

Good to see conditional joins being touted.

Another WebFOCUS hidden gem.

I created an indexed file from FILE2 and that worked. Thanks for the help.

Thus far, no one has suggested using Match File. Does Match File not work with Many-to-Many merges?

quote:

Good to see conditional joins being touted.

Conditional JOIN: 1 ... MATCH FILE: 0 (in this round)

A bit longer and convoluted but the opportunity to use: SET CARTESIAN=ON

  
EX -LINES 10 EDAPUT MASTER,MARK,C,MEM
FILENAME=MARK, SUFFIX=FOC
 SEGNAME=DUM, SEGTYPE=S1
  FIELDNAME=DUM,DUM,A1,$
 SEGNAME=R1, SEGTYPE=S1, PARENT=DUM
  FIELDNAME=ID1,ID1,I2,$
  FIELDNAME=DATE1,DATE1,MDYY,$
 SEGNAME=R2, SEGTYPE=S1, PARENT=DUM
  FIELDNAME=ID2,ID2,I2,$
  FIELDNAME=DATE2,DATE2,MDYY,$
-RUN
CREATE FILE MARK
MODIFY FILE MARK
FREEFORM DUM ID1 DATE1
MATCH DUM
ON NOMATCH INCLUDE
ON MATCH CONTINUE
MATCH ID1
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA
DUM='T',ID1=1,DATE1='01/15/2008',$
DUM='T',ID1=1,DATE1='02/17/2009',$
DUM='T',ID1=2,DATE1='04/19/2012',$
END
MODIFY FILE MARK
FREEFORM DUM ID2 DATE2
MATCH DUM
ON NOMATCH INCLUDE
ON MATCH CONTINUE
MATCH ID2
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA
DUM='T',ID2=1,DATE2='11/19/2006',$
DUM='T',ID2=1,DATE2='01/12/2012',$
DUM='T',ID2=1,DATE2='10/16/2005',$
DUM='T',ID2=2,DATE2='04/19/2003',$
DUM='T',ID2=2,DATE2='08/17/2001',$
END
 
SET CARTESIAN=ON
TABLE FILE MARK
PRINT ID1 ID2 DATE1 DATE2
BY DUM NOPRINT
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
WHERE ID1 EQ ID2;
END

@ Mark - As you may already know, use of the SUM verb instead of PRINT is the method by which one-to-many and many-to-many merges are formed with MATCH logic. In your case though, multiple dates can exist for the same key value (ID), so SUMming at the ID level only would cause dates to be lost. But adding a BY phrase at the date level to prevent losing dates would give you a merge based on ID and date combinations instead of ID only, which is not what you want. So the answer to your question is unfortuantely No, MATCH logic will not produce the desired results in this situation.

By the way, one drawback to the JOIN approach is that IDs found in FILE2 that are not also in FILE1 will be excluded from the output.

This message has been edited. Last edited by: Dan Satchell,

Thanks for confirming what I suspected about Match File in this particular case. I appreciate all the comments back. I'm new enough that I don't understand everything in all the comments but I was able to get a JOIN to work by putting an index on FILE2. I get back all possible combinations, so I'm up an running.

Does anybody know how I can indicate that my question has been resolved and to close the post?

If you scroll back to your original post (after login), there should be an icon in the lower right hand corner to EDIT the post. Click on the icon and put "[SOLVED]" at the front of the subject for the post.

I would have thought that MATCH FILE would work...but using OLD-AND-NEW instead of OLD-OR-NEW. Just need to make ID a by field. Am I missing something?

I used OLD-OR-NEW. I wanted to retain all cases from both files even if they didn't have an ID match in the other file. I used BY on ID and tried all combinations using PRINT and SUM (i.e., PRINT PRINT, PRINT SUM, SUM PRINT, AND SUM SUM). I was never able to produce a cartesian product.