Greetings,

I'm sure this is old knowledge to many, but for years I have been dogged by not understanding the FOCUS language with respect to MATCH (note, this is not MAINTAIN or MODIFY versions of MATCH). I have two hold files and I want to merge data based on a single matched key (same field name, same field type). In the code I would construct:

MATCH FILE HOLD1
PRINT FIELD1
BY KEY1
RUN
FILE HOLD2
PRINT FIELD2
BY KEY1
AFTER MATCH HOLD AS HOLD3 OLD-AND-NEW
END

The problem I encounter is that merged records in HOLD3 will only get the first occurrance of FIELD1, if there is a one-to-many relationship with that particular key value between HOLD1 and HOLD2. If FIELD1 is an alpha, subsequent merged records for that key value will be blank (if numeric, then it will be 0).

I've never been able to come across a workaround short of abandoning MATCH and instead using JOIN, and it just seems that I am missing *something* with such a useful merge facility like MATCH. Is there some elegant way to get all fields referenced in the PRINT verb from the first file propagated into the merged result hold file, not just the first match?

Thanks in advance for any clues,

-- Dan in Omaha

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

This all comes down to the use of SUM.

Try this:

MATCH FILE HOLD1
SUM FIELD1 
BY KEY1
RUN
FILE HOLD2
PRINT FIELD2
BY KEY1
AFTER MATCH HOLD AS HOLD3 OLD-AND-NEW
END

You may need to add other BY fields to HOLD2 part as well.

I'm sure the documentation explains the process and the use of SUM, take a look and see.

Hi,

Based on your description of the "problem" that you are encountering, it sounds like the relationship of the data is a many-to-many, or a many-to-one, and not a one-to-many as you say. However, if it is really a one-to-many, then Waz's solution will do the trick.

Although PRINT is valid in the first MATCH, you need to understand what it is doing "behind the scenes".
Essentially (from a simplistic point of view) your MATCH FILE is being translated into the following:

MATCH FILE HOLD1
SUM FIELD1 
BY KEY1
BY FOCLIST
RUN
FILE HOLD2
PRINT FIELD2
BY KEY1
BY FOCLIST
AFTER MATCH HOLD AS HOLD3 OLD-AND-NEW
END

i.e. "FOCLIST" is being generated as a new "BY" field, which will not happen if your first verb is a SUM.
This can generate very unpredictable results, depending on your data.

To see what is happening, change your AFTER MATCH HOLD to:
AFTER MATCH HOLD OLD-OR-NEW.
You should then see where the other occurrences of FIELD1 are going to.

My basic rules of thumb?
1) Only the last MATCH section can be a PRINT
2) The BY fields MUST be the same in NAME and FORMAT and SEQUENCE. They can obviously "grow" with each subsequent MATCH section i.e. as per Waz, you can add BY fields.
3) If I get strange results, I change my request to AFTER MATCH HOLD OLD-OR-NEW and see what FOCUS is doing behind the scenes.

In terms of your question:

quote:

Is there some elegant way to get all fields referenced in the PRINT verb from the first file propagated into the merged result hold file, not just the first match?

Perhaps you can give us a "real" example.
Offhand, I can think of two possibilities:
- Swap your files around i.e. first SUM FIELD2 BY KEY1, then PRINT FIELD1 BY KEY1
- Use JOIN

Thanks for the pointers!

The problem is in my misunderstanding of MATCH. The SUM helped out quit a bit, though I still don't grasp exactly why/what is going on in the mechanics of the verbs.

Real-world data:

HOLD1: fields INSTITUTION/A5, STDNT_GROUP/A4
Data: 
   NEUNO, FGEN
   NEUNO, BUFF

HOLD2: fields EMPLID/A8, STDNT_GROUP/A4
Data: 
   00012345, FGEN
   00078901, FGEN
   12345678, FGEN

MATCH FILE HOLD1
PRINT INSTITUTION
BY STDNT_GROUP
RUN
FILE HOLD2
PRINT EMPLID
BY STDNT_GROUP
AFTER MATCH HOLD AS HOLD3 OLD-AND-NEW
END


HOLD3:
FGEN, NEUNO, 00012345
FGEN,      , 00078901
FGEN,      , 12345678

In my (twisted) thinking, a match/merge of fields would have populated the NEUNO on all three rows, not just the first row match. I like the idea of using OLD-OR-NEW to show what FOCUS is doing behind the scenes, but it still doesn't explain why that first record gets merged with data from both sources.

HOLD3 with OLD-OR-NEW:
BUFF, NEUNO,
FGEN, NEUNO, 00012345
FGEN,      , 00078901
FGEN,      , 12345678

...For OLD-AND-NEW, if it's supposed to be everything in the old AND everything in the new, matched on the key field, then shouldn't the result set be for OLD-AND-NEW be:

FGEN, NEUNO,
FGEN,      , 00012345
FGEN,      , 00078901
FGEN,      , 12345678

...since the 'NEUNO' only exists in the old?

It was when it shows up in the merged result on the first match row with the EMPLID that I started thinking that it should propagate on all the fields where a matched result exists on the key(s).

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

I think with match there is an extra layer to the functionality.

The two files are joined with the BY fields like a SQL join, but the verbs control what happens to the verb fields. In particular the old Verb.

I like to think that you are SUMming the verb fields across the new files records.

Hi,
To then illustrate what WebFOCUS is doing when you use PRINT for both MATCH FILEs:

HOLD1 gets sorted BY STDNT_GROUP, but an internal FOCLIST gets created, so the data now has fields STDNT_GROUP, FOCLIST, INSTITUTION
Data:
BUFF,1,NEUNO
FGEN,1,NEUNO

HOLD1 gets sorted BY STDNT_GROUP, but again an internal FOCLIST gets created, so the data now has fields STDNT_GROUP, FOCLIST, EMPLID
Data:
FGEN,1,00012345
FGEN,2,00078901
FGEN,3,12345678

Note that in the above HOLD2, FOCLIST is 1,2 and 3. Why? Because FOCLIST gets numbered by the first BY field.

So, if we had to show the FOCLIST field in HOLD3, we would have:
FGEN, 1, NEUNO, 00012345
FGEN, 2, , 00078901
FGEN, 3, , 12345678

The result is perfectly correct, because PRINT forced WebFOCUS to extend the "matching" BY fields to BY STDNT_GROUP BY FOCLIST ... and FOCLIST does not match up for records 2 and 3.

Hope that makes some sense - I'm not very good with the "KISS" theory

For the above data your problem would definitely be solved by changing the first verb to SUM - because then WebFOCUS would only use STDNT_GROUP as your matching BY field.

Thank you, Twanette and Waz, for helping me understand the mechanics of MATCH. Admittedly, I had been using the online help in Dev Studio to understand MATCH (that and playing with it).

I had never seen the FOCLIST internal field before, but now it makes perfect sense. Many people in my organization avoid MATCH because of the complexity, but I always found it a useful tool for merging data, with the exception of my example "problem". Now that I understand it even better, I can appreciate its power!

Best Regards,

-- Dan in Omaha