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     Joining Multiple Hold Files and displaying their results on one report?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Joining Multiple Hold Files and displaying their results on one report?
 Login/Join
 
Gold member
posted
Here is what I am trying to do:

1. Show me how much in paid invoices I have had YTD for a specific account.

2. Show me what my remaining open PO commitments are.

I tried creating a hold file for the paid invoice totals and creating another hold file for the open PO commitment totals, and then trying to join the two hold files.

But that doesn't seem to work, am I doing this all wrong?


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Virtuoso
posted Hide Post
When creating the two hold files....do they have a common field to join? Also the Field must be indexed in the Guest file as in
ON TABLE HOLD FORMAT FOCUS AS WHATEVER INDEX WHATFIELD


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Prarie:
When creating the two hold files....do they have a common field to join? Also the Field must be indexed in the Guest file as in
ON TABLE HOLD FORMAT FOCUS AS WHATEVER INDEX WHATFIELD


Yep they have a common field of "REGION".

I did index on REGION in both HOLD files.


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Virtuoso
posted Hide Post
Ok that's good...now what's happening?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
I get EDA no Data when I try to run the whole report from start to finish.


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Virtuoso
posted Hide Post
Can you post the code please


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.SITEID IN T_PROD_WORKORDER
TO UNIQUE T_PROD_MAX_ATTRIBUTES_LOCATION.T_PROD_MAX_ATTRIBUTES_LOCATION.SITEID
IN T_PROD_MAX_ATTRIBUTES_LOCATION AS J0
END
JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.WONUM IN T_PROD_WORKORDER
TO UNIQUE T_PROD_POLINE.T_PROD_POLINE.REFWO IN T_PROD_POLINE AS J1
END
JOIN
LEFT_OUTER T_PROD_POLINE.T_PROD_POLINE.PONUM IN T_PROD_WORKORDER TO MULTIPLE
T_PROD_PO.T_PROD_PO.PONUM IN T_PROD_PO AS J2
END
JOIN
LEFT_OUTER T_PROD_POLINE.T_PROD_POLINE.PONUM IN T_PROD_WORKORDER TO MULTIPLE
T_PROD_INVOICE.T_PROD_INVOICE.PONUM IN T_PROD_INVOICE AS J3
END
TABLE FILE T_PROD_WORKORDER
SUM
T_PROD_INVOICE.TOTALCOST/P12.2CM
BY REGION
WHERE ( T_PROD_POLINE.T_PROD_POLINE.GLDEBITACCT CONTAINS '650151000002610' ) AND ( T_PROD_PO.T_PROD_PO.ORDERDATE GE DT(2008-02-03 00:00:00) );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS LANDSCAPENCPAIDS FORMAT FOCUS INDEX REGION
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
TABLE FILE T_PROD_WORKORDER
SUM
T_PROD_PO.TOTALCOST/P12.2CM
BY REGION
WHERE ( T_PROD_POLINE.T_PROD_POLINE.GLDEBITACCT CONTAINS '650151000002610' ) AND ( T_PROD_PO.T_PROD_PO.ORDERDATE GE DT(2008-02-03 00:00:00) ) AND ( T_PROD_PO.T_PROD_PO.RECEIPTS EQ 'NONE' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS LANDSCAPENCOPENPO FORMAT FOCUS INDEX REGION
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
JOIN
LEFT_OUTER LANDSCAPENCPAIDS.SEG01.REGION IN LANDSCAPENCPAIDS TO UNIQUE
LANDSCAPENCOPENPO.SEG01.REGION IN LANDSCAPENCOPENPO AS J4
END
TABLE FILE LANDSCAPENCPAIDS
SUM
TOTALCOST AS 'INVOICES'
TOTALCOST AS 'OPEN POs'
BY REGION
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Virtuoso
posted Hide Post
If you have data in both hold file for sure...try experimenting with the join....Like taking off Left out join and just have a plain join...I have to run...but someone else might come along with some more ideas.

Good Luck


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
quote:
Yep they have a common field of "REGION".

I did index on REGION in both HOLD files.


Have you considered adding account to the index as that is what it appears you want to report on.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Master
posted Hide Post
I would not index the hold files nor would I do a join. You will get nothing on your report unless you have paid invoices. Replace the join logic with MATCH logic and hold your output OLD-OR-NEW. This will return all of your data even if you don't have a matching entry. Also, remove the styling from your hold files it is unnecessary and makes your code harder to read and debug.

Good Luck


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
Gold member
posted Hide Post
The report will always have paid invoices so this is a non issue.


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Guru
posted Hide Post
Rob,

Did you check that you got a result when you ran each step as a separate fex? Add the ? HOLD for each hold file and also try adding a -SET &ECHO=ALL; to get a listing that you cna check


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
quote:
SUM
T_PROD_INVOICE.TOTALCOST/P12.2CM
BY REGION
WHERE ( T_PROD_POLINE.T_PROD_POLINE.GLDEBITACCT CONTAINS '650151000002610' ) AND ( T_PROD_PO.T_PROD_PO.ORDERDATE GE DT(2008-02-03 00:00:00) );

You are looking for a specific account, if tbere are always paid invoices how do you handle a new account?

I agree with RSquared, set &ECHO on and check your individual hold files. You might also add a ? JOIN to see if your joins worked.


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
In fact you can dispense with the two TABLE ... HOLD requests, and FORMAT FOCUS, by using MATCH for your retrievals, and then report from the merged result.

note that (comparing the WHERE clauses) the records pulled by the second leg are a subset of the first leg.

-* ... (after the four joins) ...
MATCH FILE T_PROD_WORKORDER
SUM T_PROD_INVOICE.TOTALCOST as TOTAL_INV
BY REGION
WHERE ( T_PROD_POLINE.T_PROD_POLINE.GLDEBITACCT CONTAINS '650151000002610' )
AND ( T_PROD_PO.T_PROD_PO.ORDERDATE GE DT(2008-02-03 00:00:00) );
RUN
FILE T_PROD_WORKORDER
SUM T_PROD_PO.TOTALCOST AS TOTAL_OPEN
BY REGION
WHERE ( T_PROD_POLINE.T_PROD_POLINE.GLDEBITACCT CONTAINS '650151000002610' )
AND ( T_PROD_PO.T_PROD_PO.ORDERDATE GE DT(2008-02-03 00:00:00) )
AND ( T_PROD_PO.T_PROD_PO.RECEIPTS EQ 'NONE' );
AFTER MATCH HOLD OLD
END

JOIN CLEAR

TABLE FILE HOLD
SUM
TOTAL_INV/P12.2CM   AS 'INVOICES'
TOTAL_OPEN/P12.2CM  AS 'OPEN POs'
BY REGION
-* ... styling omitted ...
END  


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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     Joining Multiple Hold Files and displaying their results on one report?

Copyright © 1996-2020 Information Builders