Focal Point
Joining Multiple Hold Files and displaying their results on one report?

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7301003382

May 07, 2008, 04:44 PM
Rob M.
Joining Multiple Hold Files and displaying their results on one report?
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
May 07, 2008, 05:10 PM
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


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
May 07, 2008, 05:17 PM
Rob M.
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
May 07, 2008, 05:20 PM
Prarie
Ok that's good...now what's happening?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
May 07, 2008, 05:26 PM
Rob M.
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
May 07, 2008, 05:40 PM
Prarie
Can you post the code please


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
May 07, 2008, 05:47 PM
Rob M.
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
May 07, 2008, 05:58 PM
Prarie
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
May 08, 2008, 08:47 AM
Leah
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
May 08, 2008, 09:28 AM
PBrightwell
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
May 08, 2008, 10:12 AM
Rob M.
The report will always have paid invoices so this is a non issue.


Rob M.
Target Corporation

WF 7.1.4
May 08, 2008, 10:26 AM
RSquared
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
May 09, 2008, 12:50 PM
PBrightwell
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
May 09, 2008, 02:14 PM
j.gross
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