Hello everybody,
I will explain the problem shortly :

I have to perform a specific type of merging two tables - e.g. lets say for example we have the following tables:

TABLE1 TABLE2
------ -------

COLUMN_1 COLUMN_2
------------- -------------
RECORD_1 (*) RECORD_1 (*)
RECORD_1 (**) RECORD_1 (**)
RECORD_2 (*) RECORD_1 (***)
RECORD_2 (**) RECORD_2 (*)
RECORD_2 (***) RECORD_2 (**)
RECORD_4 (*) RECORD_3 (*)

So what I am trying to do is to merge the two tables ( lets say the TABLE1 is the master and TABLE2 is the detail ) so that only the first unique record in TABLE1 is matched ot all similar records in TABLE2 and the other records ( together with all other unmatched in TABLE1 and TABLE2 ) are printed as well. In the example given ( the '*' only indicates what of the same records must be matched ) the result should be something like :

MATCHED_TABLE:

COLUMN_1 COLUMN_2
-------- -----------

RECORD_1 (*) RECORD_1 (*)
RECORD_1 (*) RECORD_1 (**)
RECORD_1 (*) RECORD_1 (***)
RECORD_1 (**) ___________ - unmatched in master
RECORD_2 (*) RECORD_2 (*)
RECORD_2 (*) RECORD_2 (**)
RECORD_2 (**) ___________ - unmatched in master
RECORD_2 (***) ___________ - unmatched in master
RECORD_4 (*) ___________ -unmatched in master
___________ RECORD_3 (*)-unmatched in detail

I've tried using a combination of the join and match command but only with a close similarity to the result expected.
Any suggestions to the problem would be very appreciated.
Thanks in advance.

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

Try this:

 
MATCH FILE MFD1
SUM COL1
BY COL1 AS JOINFLD
RUN
      FILE MFD2
SUM COL2
BY COL2 AS JOINFLD
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD
PRINT COL1 AS 'IN FILE 1'
      COL2 AS 'IN FILE 2'
BY JOINFLD
END
 

Hope this helps ...

Use MATCH and try SUM FST.fieldname BY record from the first table followed by PRINT fieldnames BY record from the second table.

T

I don't think MATCH will work. MATCH won't pick up the 3 records for RECORD2 that match the first record for RECORD1.

I would think a join with a SET ALL=ON makes more sense.

Thanks very much for the replies - MATCH that uses SUM with FST. and then PRINT for the second table is a close decision but another problem occurs - the similar records in the master are not printed.

( RECORD_1 (**) ___________ - unmatched in master
RECORD_2 (**) ___________ - unmatched in master
RECORD_2 (***) ___________ - unmatched in master )

so if there is some way to include them would be great.
I suppose using MORE after taking the similar records in the master table and adding empty columns would solve fully the problem but I was wondering if it could be done only with a MATCH block.

Ginny: I thought about that but a JOIN TO ALL would join the similar records in the master to all the corresponding records in the detail. And maybe that way a decision could arise but it won't be short either way.

WF 7.6.5 / OS: XP / FOCUS

Sounds like a McGyver case if the fields you want are the main ones.

T

What you have advised me + filtering and MORE does the job.


e.g. for the example j.gross gave :

MATCH FILE FILE1
  SUM FST.DATA1 ...
  BY KEY1 AS KEY
RUN
FILE FILE2
  PRINT DATA2 ...
  BY KEY2 AS KEY
AFTER MATCH HOLD AS TMP1 OLD-OR NEW
END
-RUN

-*** FILTERING THOSE LEFT IN THE MASTER AND SKIPPED IN THE MATCH
DEFINE FILE FILE1
<TEMP COLUMNS THAT ARE MISSING IN FILE1 BUT ARE IN FILE2 > = '';
END
-RUN

TABLE FILE FILE1
PRINT *
<+ COLUMNS FROM DEFINE>
WHERE <BY_FILEDS> EQ LAST <BY_FIELDS> 
ON TABLE HOLD AS UNMATCHED_MASTER
END
-RUN
TABLE FILE TMP1
PRINT *
ON TABLE HOLD AS TMP1
MORE
FILE UNMATCHED_MASTER
END
-RUN

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

To accomplish that, use
- an initial HOLD to distinguish first vs. later rows for each key in Master,
- a pair of Match files (to hold the first Master record merged against Detail records, and separately hold the remaining master records),
- and finally MORE to combine the MATCH outputs:

-* PULL SPECIMEN MASTER AND DETAIL DATA
TABLE FILE CAR
  COUNT ENTRIES NOPRINT
  BY SEATS AS KEY1
  BY COUNTRY AS ALPHA
  BY CAR     AS BETA
  IF COUNTRY EQ ITALY OR JAPAN
  ON TABLE SET ASNAMES ON AND HOLDLIST PRINTONLY
  ON TABLE HOLD AS MASTER
RUN
  COUNT ENTRIES NOPRINT
  BY SEATS AS KEY2
  BY COUNTRY AS GAMMA
  BY CAR     AS DELTA
  IF COUNTRY EQ ENGLAND
  ON TABLE SET ASNAMES ON AND HOLDLIST PRINTONLY
  ON TABLE HOLD AS DETAIL
END
-RUN

-* now down to business:
-* (SEATS is the "Key"; Key1 and Key2 are included in the matches and final result for clarity)

-* 1. Add sequence number (LIST) to Master record. --> HOLD1
TABLE FILE MASTER
  LIST ALPHA BETA
  BY KEY1
ON TABLE HOLD AS HOLD1
END
-RUN

-* 2. Merge first Master with Detail recordS. --> HOLD2
MATCH FILE HOLD1
  WRITE ALPHA BETA KEY1
  BY KEY1 AS KEY
  IF LIST EQ 1
RUN
FILE DETAIL
  PRINT GAMMA DELTA KEY2
  BY KEY2 AS KEY
  AFTER MATCH HOLD AS HOLD2      OLD-OR-NEW
END
-RUN

-* 3. Nominal "Merge" of the other Master rows with a null Detail record. --> HOLD3, same columns as 2
MATCH FILE HOLD1
  PRINT ALPHA BETA KEY1
  BY KEY1 AS KEY
  IF LIST GE 2
RUN
FILE DETAIL
  PRINT GAMMA DELTA KEY2
  BY KEY2 AS KEY
  IF KEY2 EQ 0
  IF KEY2 NE 0
  AFTER MATCH HOLD AS HOLD3      OLD
END
-RUN

-* 4. combine:
TABLE FILE HOLD2
  PRINT KEY1 ALPHA BETA 
        KEY2 GAMMA DELTA 
  BY KEY UNDER-LINE
MORE
FILE HOLD3
END

Thanks a lot ! If any problems occur later ( probably not ) I will post it here.

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