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     [SOLVED] How to merge two hold files.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to merge two hold files.
 Login/Join
 
Pku
Silver Member
posted
Hi all,

I have two hold files: HOLD1, HOLD2 which contains some IDs of HOLD1. After merging, the file should have all data of HOLD1, plus some data of HOLD2 which its IDs are in HOLD1.

Example:

HOLD1:
ID  DATE        AMOUNT
11  09/30/2008  100
22  09/30/2008  200
33  09/30/2008  300

HOLD2:
ID  DATE        AMOUNT
11  08/31/2008  110
12  08/31/2008  120
13  08/31/2008  123 
22  08/31/2008  234
33  08/31/2008  333
  
The result needs to be:

ID  DATE        AMOUNT
11  09/30/2008  100
11  08/31/2008  110
22  09/30/2008  200
22  08/31/2008  234
33  09/30/2008  300
33  08/31/2008  333


Thank you,
Pku

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


Thanks,
Pku

Focus, WebFocus 8201 on Windows
 
Posts: 43 | Registered: May 28, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
You can accomplish it with Match File (two merges, three data sources) as sketched below:

file hold1: by ID
file hold2: by id by date by amount
hold old-and-new
run
(yields the rows of hold2 with matching ID in hold1)

file hold1: by id by date by amount
hold old-or-new
end
(merges any rows of hold1 not already obtained from hold2)


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Microfich
Gold member
posted Hide Post
One way:

  
DEFINE FILE HOLD1
H1/A1 = '1';
END

MATCH FILE HOLD1
PRINT DATE AS DATE1 AMOUNT AS AMOUNT1 H1
BY ID
RUN
FILE HOLD2
PRINT DATE AS DATE2 AMOUNT AS AMOUNT2  
BY ID
AFTER MATCH HOLD AS HOLD3 OLD
END
-RUN

DEFINE FILE HOLD3
DATE/MDYY = IF H1 EQ '1' THEN DATE1 ELSE DATE2;
AMOUNT/I9 = IF H1 EQ '1' THEN AMOUNT1 ELSE AMOUNT2;
END

TABLE FILE HOLD3
PRINT ID DATE AMOUNT
END


WebFOCUS 8105
Windows;
DB2, UDB, SQL Server, Oracle
FOCUS-WebFOCUS since 1981
 
Posts: 84 | Registered: December 13, 2005Report This Post
Doug
Expert
posted Hide Post
I like the alternatives that you provided Jack. Afterall, anything is possible...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Pku
Silver Member
posted Hide Post
Thanks, Jack and Microfich.

I used Jack's way with a bit of modification since the IDs could not be duplicated.
After matching HOLD2 to HOLD1 as HOLD3 as "OLD-AND-NEW". Then, I used "MORE" to merge HOLD1 back to HOLD3 to get the result.

  
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS FINAL
MORE
FILE HOLD3
END


Thanks again,
Pku


Thanks,
Pku

Focus, WebFocus 8201 on Windows
 
Posts: 43 | Registered: May 28, 2008Report 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     [SOLVED] How to merge two hold files.

Copyright © 1996-2020 Information Builders