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     Combining HOLD Files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Combining HOLD Files
 Login/Join
 
Platinum Member
posted
I have created 2 hold files that hold the same fields in each one except one hold information for a prior year verses the other holds information for the previous month. I want to combine these two then get all the duplicates found in the previous year that match with the previous month. But everytime I do a match or join I only get information for one or the other. I hope this makes sense. Any help would be greatly appriciated.

Thank you.


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
Try Universal Concatenation, i.e. the MORE command. That should do what you want.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
Did you try OLD-AND-NEW in a MATCH?



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Platinum Member
posted Hide Post
I tried every single one in the match with no luck. I will look into the Universal Concatenation GinnyJakes is suggesting next. :-)


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
MATCH puts things side-by-side. MORE stacks things. Since you have identical masters, MORE will work best.

There are other operating system things that I could suggest later if that doesn't work for you, Latigresa.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
...or you could append the 2 hold files:

FILEDEF HCAR DISK HCAR.FTM
-RUN

TABLE FILE CAR
PRINT 
CAR
BY COUNTRY
BY MODEL
WHERE COUNTRY EQ 'W GERMANY'
ON TABLE HOLD AS HCAR
END
-RUN

FILEDEF HCAR DISK HCAR.FTM (APPEND
-RUN

TABLE FILE CAR
PRINT 
CAR
BY COUNTRY
BY MODEL
WHERE COUNTRY EQ 'ITALY'
ON TABLE HOLD AS HCAR
END
-RUN

TABLE FILE HCAR
PRINT *
END


Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Platinum Member
posted Hide Post
I have not had any luck. No matter what I have tried I still only get the results for one of the 2 tables. I am going to call in instead. Thanks everyone.

:-)


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Gold member
posted Hide Post
I guess you there's an issue with the format of hold files. When you try to concatenate 2 holds with mismatch in format.. You will retrieve only first hold file data.

Try using this

?FF HOLD1
?FF HOLD2
-RUN
Make sure the formats match & next try MORE statement.


Prod: WF 7.6.10 windows. -- MRE/Dashboard/Self Service/ReportCaster - Windows XP
 
Posts: 82 | Location: Chicago | Registered: September 28, 2005Report This Post
Virtuoso
posted Hide Post
If you post the fex code we might be able to help you better.
But the code in between [CODE[ [/CODE[ the last characters [ should be ], for better readability.
The format of the hold files is important. (focus, flatfiles) If you want to join you need the correct matching keyfields.




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
Platinum Member
posted Hide Post
Here is the code that I finally got a little bit of the correct results I need but I'm still missing all the ones for the prior year that match what is in the current month. I'm able to find out which VIN # are duplicated this way but I need to get all of the claim #s for the ones in the prior year. I hope I have not confused you more.

APP HOLD ANA
MATCH FILE HOLD_CL_MONTHLY_VIN
BY VEH_ID_NO
BY CLM_NO
BY CLMNT_TYPE
BY INSD_NAME
BY DCM_NO
BY ADJ_EMP_NO
BY CLMNT_NO
BY DT_OF_ACC
RUN
FILE HOLD_ALL_VINS
BY VEH_ID_NO
BY CLM_NO
BY CLMNT_TYPE
BY INSD_NAME
BY DCM_NO
BY ADJ_EMP_NO
BY CLMNT_NO
BY DT_OF_ACC
AFTER MATCH HOLD AS AMI_TEST_CL OLD-OR-NEW
END
TABLE FILE AMI_TEST_CL
SUM
'CNT.AMI_TEST_CL.AMI_TEST.VEH_ID_NO' AS 'VIN_COUNT'
BY 'AMI_TEST_CL.AMI_TEST.VEH_ID_NO'
PRINT
'AMI_TEST_CL.AMI_TEST.CLM_NO'
'AMI_TEST_CL.AMI_TEST.CLMNT_TYPE'
'AMI_TEST_CL.AMI_TEST.CLMNT_NO'
'AMI_TEST_CL.AMI_TEST.DT_OF_ACC'
BY 'AMI_TEST_CL.AMI_TEST.VEH_ID_NO'
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_MATCH_ALL FORMAT FOCUS INDEX AMI_TEST_CL.AMI_TEST.VEH_ID_NO
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END
TABLE FILE HOLD_MATCH_ALL
PRINT
'HOLD_MATCH_ALL.SEG01.VEH_ID_NO'
'HOLD_MATCH_ALL.SEG01.VIN_COUNT'
'HOLD_MATCH_ALL.SEG02.CLM_NO'
'HOLD_MATCH_ALL.SEG02.CLMNT_TYPE'
'HOLD_MATCH_ALL.SEG02.CLMNT_NO'
'HOLD_MATCH_ALL.SEG02.DT_OF_ACC'
WHERE ( HOLD_MATCH_ALL.SEG02.DT_OF_ACC EQ '20071101' OR '20071102' OR '20071103' OR '20071104' OR '20071105' OR '20071106' OR '20071107' OR '20071108' OR '20071109' OR '20071110' OR '20071111' OR '20071112' OR '20071113' OR '20071114' OR '20071115' OR '20071116' OR '20071117' OR '20071118' OR '20071119' OR '20071120' OR '20071121' OR '20071122' OR '20071123' OR '20071124' OR '20071125' OR '20071126' OR '20071127' OR '20071128' OR '20071129' OR '20071130' ) AND ( HOLD_MATCH_ALL.SEG01.VIN_COUNT GT 1 );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_MATCH_ALL_1 FORMAT FOCUS INDEX HOLD_MATCH_ALL.SEG01.VEH_ID_NO



Thank you very much.
Ana


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
Ana,

MATCH is not going to work for you. MATCH puts the contents of two files side-by-side, not one after the other which is what you want.

Since you did an APP HOLD, could you please post the masters for HOLD_CL_MONTHLY_VIN and for HOLD_ALL_VINS so that we can see them?

Once you have the files stacked, then you can do data comparisons.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
quote:
WHERE ( HOLD_MATCH_ALL.SEG02.DT_OF_ACC EQ '20071101' OR '20071102' OR '20071103' OR '20071104' OR '20071105' OR '20071106' OR '20071107' OR '20071108' OR '20071109' OR '20071110' OR '20071111' OR '20071112' OR '20071113' OR '20071114' OR '20071115' OR '20071116' OR '20071117' OR '20071118' OR '20071119' OR '20071120' OR '20071121' OR '20071122' OR '20071123' OR '20071124' OR '20071125' OR '20071126' OR '20071127' OR '20071128' OR '20071129' OR '20071130' )


It was not your question but how about:

WHERE ( EDIT(HOLD_MATCH_ALL.SEG02.DT_OF_ACC,'999999') EQ '200711';

it is a lot shorter

Or

WHERE ( HOLD_MATCH_ALL.SEG02.DT_OF_ACC LIKE '200711$*');

And there are more solutions if this field is a smartdate.




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
Master
posted Hide Post
Ana,
Since you are matching on this field:
quote:
BY DT_OF_ACC


you are not really putting your data into one record. Your 2007 data for a VIN will be in one reocrd and your 2006 will be in another. You are then eliminating the 2006 records in your where. If I am understanding the problem, you want all Nov 2007 data and all 2006 where the VIN is in the Nov file. Make sure that file HOLD_ALL_VINS is indexed on VEH_ID_NO. Then try:
[code]
SET ALL=PASS
JOIN VEH_ID_NO IN HOLD_CL_MONTHLY_VIN TAG MO TO ALL VEH_ID_NO IN HOLD_ALL_VINS TAG YR AS J1
TABLE FILE HOLD_CL_MONTHLY_VIN
PRINT
YR.CLM_NO
YR.CLMNT_TYPE
(etc)
BY MO.VEH_ID_NO
BY MO.CLM_NO
(etc)
END
[\code]
TAG is optional, but it will let you designate which file the fields are coming from without having to use the entire filename as a prefix. The code is simplified, you will need to figure out what you want from there. I would recommend that you try it pretty much the way it is written (without the etc) so that you can see if you are getting the records you want as output and then expand on it.


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
Platinum Member
posted Hide Post
Thanks everyone!!! I have gotten the results I needed. Hope everyone has a great day!

Ana


WEBFOCUS 7.6.4
Server: WINXP
 
Posts: 121 | Registered: September 20, 2007Report 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     Combining HOLD Files

Copyright © 1996-2020 Information Builders