Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Question about MATCH logic

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Question about MATCH logic
 Login/Join
 
Guru
posted
I've got two datasets that I want to do some MATCH logic against. Both datasets can contain the same data (i.e., perhaps an Employee ID. I want to end up having only one dataset, containing data from dataset 1 and if there is a match in dataset 2, excluding the matching record found in dataset 2.

My question: to get all records from dataset 1 and to exclude matching records from dataset 2, however, to also include all non-matching records from dataset 2. Would I use OLD-NOT-NEW or would I use OLD-NOR-NEW?

In my program, I am not seeing records from dataset1, and am curious as to what I might be doing wrong.

Here's my piece of code for trying to work this MATCH logic:

  MATCH FILE S079Y&INSTX
PRINT
     TX_GRANT
     TXGRANT1 TXGRANT2 TXGRANT3 TXGRANT4
-*   AWD_PERIOD
     AWARDYR
BY   STU_ID
BY   HIGHEST TX_GRANT NOPRINT
BY   AWD_PERIOD   NOPRINT
RUN
FILE S079X&INSTX
PRINT
     TX_GRANT
     TXGRANT1 TXGRANT2 TXGRANT3 TXGRANT4
-*   AWD_PERIOD
     AWARDYR
BY   STU_ID
BY   HIGHEST TX_GRANT NOPRINT
BY   AWD_PERIOD   NOPRINT
AFTER MATCH HOLD OLD-NOT-NEW
END
-RUN
-*


Thank you in advance to all who answer...looking forward to hearing from you!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Virtuoso
posted Hide Post
OLD-NOT-NEW will get what is in old ( the first file) that is 'not' in new. Good old financial aid.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Virtuoso
posted Hide Post
I would say OLD-NOT-NEW but I wonder why you do two BY's with NOPRINT.
I would suggest to remove these and see what you get.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Guru
posted Hide Post
Wow! You folks are fast!!Thank you all so very much for your offers of help and advice! I'll incorporate as much as possible from your comments and see what comes out the other side.

You all are definitely appreciated!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Guru
posted Hide Post
Tom Flynn,

I didn't understand your 3 lines of code at the end of the sample you posted... the lines that say,

ON TABLE HOLD AS GOT_EM
MORE
FILE HOLD2

If possible, can you please explain what they do, or did you just put them in as "place holders?"

Thanks again!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Virtuoso
posted Hide Post
Webmeister,
If I read you correctly, you have 2 files with exactly the same fields and you want to take only the unmatching records: from the first file records which do not have a match with the second file and vice-versa. This is the OLD-NOR-NEW option.
What you are going to be left with is a HOLD file with the BY fields and 2 sets of PRINT or SUM fields with the same names. Also when there is data in the first set, the second set wil be blank or zero and vice-versa. So I would do the following:
  
MATCH FILE S079Y&INSTX
PRINT
     TXGRANT1 TXGRANT2 TXGRANT3 TXGRANT4
     AWARDYR
BY   STU_ID
BY   TX_GRANT 
BY   AWD_PERIOD   
RUN
FILE S079X&INSTX
PRINT
     TXGRANT1 TXGRANT2 TXGRANT3 TXGRANT4
     AWARDYR
BY   STU_ID
BY   TX_GRANT
BY   AWD_PERIOD
AFTER MATCH HOLD AS ABC OLD-NOR-NEW
END


The HOLD file MASTER would look like this
  
FILENAME=ABC, SUFFIX=FIX
SEGNAME=ABC
FIELDNAME=STU_ID, ...
FIELDNAME=TX_GRANT, ...
FIELDNAME=AWD_PERIOD, ...
FIELDNAME=TXGRANT1, ... 
FIELDNAME=TXGRANT2, ... 
FIELDNAME=TXGRANT3, ... 
FIELDNAME=TXGRANT4, ...
FIELDNAME=AWARDYR, ...
FIELDNAME=TXGRANT1, ... 
FIELDNAME=TXGRANT2, ... 
FIELDNAME=TXGRANT3, ... 
FIELDNAME=TXGRANT4, ...
FIELDNAME=AWARDYR, ...

Prepare another MASTER which would look like this:
  
FILENAME=ABC, SUFFIX=FIX
SEGNAME=ABC
FIELDNAME=STU_ID, ...
FIELDNAME=TX_GRANT, ...
FIELDNAME=AWD_PERIOD, ...
SEGNAME=TX, PARENT=ABC, OCCURS=VARIABLE
FIELDNAME=TXGRANT1, ... 
FIELDNAME=TXGRANT2, ... 
FIELDNAME=TXGRANT3, ... 
FIELDNAME=TXGRANT4, ...
FIELDNAME=AWARDYR, ...

This MASTER creates 2 logical records for each physical one.

Then you can get all your records from ABC (I assume that the filed AWARDYR is numeric, if it alpha, test against a space):
  
TABLE FILE ABC
PRINT
     TXGRANT1 TXGRANT2 TXGRANT3 TXGRANT4
     AWARDYR
BY   STU_ID
BY   TX_GRANT
BY   AWD_PERIOD
WHERE AWARDYR NE 0
END


I hope this is clear.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
<peyton>
posted
webmeister
In a MATCH paragraph, the second verb always has to be SUM, even tho it may make no particular sense to the developer at the moment.
In your example, you second verb is PRINT (as is your first, which is fine).
 
Report This Post
Virtuoso
posted Hide Post
With MATCH, having PRINT/PRINT is fine. In fact any order of the verbs is legal, though perhaps not always logical.

Compare
MATCH FILE CAR
PRINT SALES
BY COUNTRY
RUN
FILE CAR
SUM SALES
BY COUNTRY
BY CAR
END
TABLE FILE HOLD
PRINT *
END

with
MATCH FILE CAR
SUM SALES
BY COUNTRY
RUN
FILE CAR
PRINT SALES
BY COUNTRY
BY CAR
END
TABLE FILE HOLD
PRINT *
END

It is more logical to have PRINT verbs after SUM verbs, but it is not syntactically incorrect to have it the other way around.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Guru
posted Hide Post
Again, thank you all for replying so quickly! I'll try to assimilate your replies and apply your advice to my project.

Tom Flynn,
I'm still not clear on those bottom lines of code:
quote:
ON TABLE HOLD AS GOT_EM
MORE
FILE HOLD2


I understand the ON TABLE HOLD AS GOT_EM, however,

what do the two lines after that do? What does MORE do? what does FILE HOLD2 do?

Thank you again to all of you for your willingness to help - I appreciate it very much!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Guru
posted Hide Post
Thanks, Tom,

It just gets confusing to me, when I see HOLD AS GOT_EM followed by FILE HOLD2. In other words, my confusion stems from holding a file with one name, but then following that with a totally different file name.

I appreciate you replying so quickly also....many thanks.


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Master
posted Hide Post
quote:
My question: to get all records from dataset 1 and to exclude matching records from dataset 2, however, to also include all non-matching records from dataset 2. Would I use OLD-NOT-NEW or would I use OLD-NOR-NEW?


I may be the only one reading this different. I take this to mean you want all of the records in your "OLD" dataset and the ones from the "NEW" dataset that do not match. I agree with Tom that you need to run through the data twice, but I do not agree how to go about it. Tom's output would be the same as OLD-NOR-NEW. The set that doesn't match.
STEP 1
What you want in your first iteration is NEW-NOT-OLD which will give you the set from the NEW file that is not on the OLD file.
STEP 2
Then you either want to concatenate the HOLD file from step 1 to your original "OLD" file(MORE) or MATCH your original "OLD" file to the HOLD file from step 1 as the "NEW" file and hold OLD-AND-NEW.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Webmeister,
This is what you said at the beginning:
quote:

I want to end up having only one dataset, containing data from dataset 1 and if there is a match in dataset 2, excluding the matching record found in dataset 2.

My question: to get all records from dataset 1 and to exclude matching records from dataset 2, however, to also include all non-matching records from dataset 2.

So tell us what you want:
Records from 1 that don't match with 2.
Records from 2 that don't match with 1.
Do both files have the same fields?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Guru
posted Hide Post
Pat,

I'll try your comments and see what I get as a result....thanks!

Daniel,

What I want is to have ALL the records from Dataset 1, plus only those records from Dataset 2 that do not match the records in Dataset 1.
And yes, both files have the same fields.
Thank you for replying also!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Virtuoso
posted Hide Post
Webmeister,
Ok. I suggest you combine what I wrote above with Tom's MORE.
  
TABLE FILE ABC
PRINT
     TXGRANT1 TXGRANT2 TXGRANT3 TXGRANT4
     AWARDYR
BY   STU_ID
BY   TX_GRANT
BY   AWD_PERIOD
WHERE AWARDYR NE 0
ON TABLE HOLD AS WEBMEISTER
MORE
FILE S079Y&INSTX
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Guru
posted Hide Post
Daniel,

Shalom and manishma! What I ended up doing, after all of this MATCH logic, was to discard all of the MATCH logic and instead to use HIGHEST and MAX logic. That got my data into the sequence I was looking for and then I was able to use FST code.

I'm going to be out of the office for a couple of days, but if your'e interested, I can send you the piece of code that I ended up using to make my program work.

How is Tel Aviv? I had a job in the Sinai years ago and during my first few months, stayed at the Hotel Dan in your city.... I enjoyed Tel Aviv tremendously.

Thank you for your help!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
 
Posts: 250 | Registered: January 14, 2008Report This Post
Virtuoso
posted Hide Post
Webmeister,
Sure. Send me your code at my e-mail (see profile).
`Hag Samea`h!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Question about MATCH logic

Copyright © 1996-2020 Information Builders