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] Looking to do a UNION of Hold Files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Looking to do a UNION of Hold Files
 Login/Join
 
Member
posted
Developing a report in App Studio and the report requirement involves presenting 3 summary rows of data in a single grid.
For example: the First row is All customer sales over time, the Second row is for sales related to Top 20 customers over time and the Third row is sales for All Other customers over time. The data is not a straight pull from the database so 3 separate HOLD Files were built for each of the above cases. All 3 Hold files have the same fields/columns and are the same format. The plan is to pull all this data back together in another Hold file and build the final version of the report.

If I was writing this in SQL stored proc I would do a UNION of the 3 datasets since each contain the same fields.
My question; what is the proper menthod to use in WebFOCUS. I have tried Join and Match and neither give me one table with all three datasets in one union at the same level.

Suggestions or examples appreciated.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 8.009
Windows, All Outputs
 
Posts: 13 | Registered: January 15, 2014Report This Post
Expert
posted Hide Post
Hi Dave,

Perhaps one of the easiest methods would be to FILEDEF ddname DISK filename.ext (APPEND.

If you use ON TABLE HOLD AS ddname FORMAT ALPHA for each hold file you should be able to perform a single table request on the resultant file.

Alternatively you could use MORE with your existing 3 hold files.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
quote:
loping a report in App Studio and the report requirement involves presenting 3 summary rows of data in a single grid.

-----------------------------------
After generating your all hold file use more command for UNION :-

TABLE FILE hold1
PRINT field1
BY field2
ON TABLE HOLD AS final_hold
MORE
FILE hold2
MORE
FILE hold3
END
-RUN
TABLE FILE final_hold
PRINT field1
BY field2
END
-EXIT


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Report This Post
Gold member
posted Hide Post
Another option is to concatenate the hold files via the USE statement, as long as each file has identical columns and column sizes (i.e. master files are identical) and they are held as FOCUS or XFOCUS files.

For example:

USE hold1 AS hold1
    hold2 AS hold1
    hold3 AS hold1
END

TABLE FILE hold1
PRINT field1
      field2
.
.
.
END


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Member
posted Hide Post
Thanks everyone. One additional item I found out is the use wants the report when finished in Report Caster.
Are are any expected issues with using any of these solutions?


WebFOCUS 8.009
Windows, All Outputs
 
Posts: 13 | Registered: January 15, 2014Report This Post
Virtuoso
posted Hide Post
Hi Dave,
What do you mean by
quote:
the use wants the report when finished in Report Caster
?

The above technics shows how to "merge" data files together to be able to use that new file for something else such as producing a report.

If your goal is to create a new file that users will then be able to use by themselves, you'll have to save (APP HOLD ...) this file somewhere on the server and make it available.

You may need to do more research about this and have some WF training.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Hi dave,

i faced the similar requirement in one of my project. Please try as below

TABLE FILE FINAL_HOLD_GRP
PRINT *
ON TABLE HOLD AS FINAL
MORE
FILE ONE_FILE
MORE
FILE SECOND_FILE
END


The final hold file will give the union set of al the three file. You can pull the data and use it for further reporting.


WebFOCUS 8
Windows, All Outputs
 
Posts: 8 | Registered: March 11, 2015Report This Post
Member
posted Hide Post
Thanks everyone.
I was able to use the 'MORE' command and successfully joined the data I needed together.
The report also appears to run just fine in Report Caster.


WebFOCUS 8.009
Windows, All Outputs
 
Posts: 13 | Registered: January 15, 2014Report This Post
Virtuoso
posted Hide Post
Update the subject of your first post by adding [SOLVED] at the beginning.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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] Looking to do a UNION of Hold Files

Copyright © 1996-2020 Information Builders