[CLOSED] Need to merge 3 hold files and create report from merge
I have looked at some posts on the forum for merging but nothing I tried works. I would do a union or create views in sql but I'm stuck in WF.
I need to merge rows from 3 different hold files. example I have 55 rows in Renewals and 30 rows in endorsements and 88 rows in AllQueue. All 3 hold files have the same fields. I need to make a single file with 173 rows that I can then report from.
My precedure does a join of multiple master files, creates a hold file "renewals" does a define and creates next hold file "endorsements" does a define and creates last hold file "allqueue"
code for tables: TABLE FILE WKFLOW_DOC_TYPE_FACT SUM J3.TRAN_TYPE_DIM.TRAN_TYPE_NM AS 'Type' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DISPLAYMMYY AS 'DocDate' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.ACTIVE AS 'Active' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.PEND AS 'Pend' BY LOWEST J0.USER_DIM.USER_ID AS 'UserID' BY LOWEST J2.NODE_DIM.NODE_NM AS 'Node' BY LOWEST WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DISPLAYMMYY NOPRINT AS 'CalcDate' WHERE ( J3.TRAN_TYPE_DIM.TRAN_TYPE_CD EQ 'R' ) AND ( WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DT_KEY EQ '09/29/2011' ); ON TABLE HOLD AS RENEWALS FORMAT FOCUS END
TABLE FILE WKFLOW_DOC_TYPE_FACT SUM COMPUTE OLDEND_DATE/MDYY = MIN.WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.OLDENDDATE; NOPRINT AS 'CalcDate' J3.TRAN_TYPE_DIM.TRAN_TYPE_NM AS 'Type' COMPUTE OLDEND_DATE/MDYY = MIN.WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.OLDENDDATE; AS 'DocDate' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.ACTIVE AS 'Active' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.PEND AS 'Pend' BY LOWEST J0.USER_DIM.USER_ID AS 'UserID' BY LOWEST J2.NODE_DIM.NODE_NM AS 'Node' WHERE ( J3.TRAN_TYPE_DIM.TRAN_TYPE_CD EQ 'E' ) AND ( WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DT_KEY EQ '09/29/2011' ); ON TABLE HOLD AS ENDORSEMENTS FORMAT FOCUS END
TABLE FILE WKFLOW_DOC_TYPE_FACT SUM WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DISPLAYMMYY NOPRINT AS 'CalcDate' AllQueue AS 'Type' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.AllQueueDate AS 'DocDate' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.ACTIVE AS 'Active' WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.PEND AS 'Pend' BY LOWEST J0.USER_DIM.USER_ID AS 'UserID' BY LOWEST J2.NODE_DIM.NODE_NM AS 'Node' WHERE WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DT_KEY EQ '09/29/2011'; ON TABLE HOLD AS ALLQUEUE FORMAT FOCUS ENDThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.7.03 Windows 7
October 19, 2011, 01:44 PM
njsden
You can try;
quote:
How to Concatenate Data Sources USE fileid-1 AS mastername fileid-2 AS mastername . . fileid-n AS mastername END where:
fileid-1...fileid-n Are any valid file specifications for the files being concatenated.
mastername Is the name of the Master File that describes the data sources.
or:
quote:
How to Concatenate Data Sources The MORE phrase, which is accessible within the TABLE and MATCH commands, specifies how to concatenate data from sources with dissimilar Master Files.
{TABLE|MATCH} FILE file1 main request MORE FILE file2 subrequest MORE FILE file3 subrequest MORE . . . {END|RUN}
Search for 'Concatenate Data Sources' in Developing Reporting Applications documentation manual (DN# 4501038.0511). You will find some ideas and examples that might help.
If the three hold files have the same column names and same field formats, why not just do a dynamic concat? The records will all be placed onto table1 as new rows, creating the one table with 173 rows you are looking for.
WebFocus 7.7.03 - 7703_hotfix - Gen: 284 Dev Studio 7.7.03 - Gen: 06062011 Mainframe Focus 7.7.03 Windows WF Client Server zOS Reporting server (hub-sub config) Windows Reporting server Windows RC Client Server