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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Merging table
 Login/Join
 
Member
posted
Got stuck again, yay~ Mad

So I got this table :
Day     QTY
1       15
2       10
3       20
4       0
...
30      0
31      0


and this table :
Day     QTY
4       35


need to join the two so it comes out like :
Day     QTY
1       15
2       10
3       20
4       35
...
30      0
31      0


anyone know how to do this? Tried using CMD COPY HOLD1 + HOLD2 HOLD3 but it came out :
Day     QTY
1       15
2       10
3       20
4       0
...
30      0
31      0
4       35


thanks~ Smiler Smiler Smiler


7.1.3 on XP into HTML, Excel, PDF
 
Posts: 27 | Registered: April 15, 2008Report This Post
Expert
posted Hide Post
Yuuta,

Take your combined file and -

TABLE FILE HOLD3
SUM QTY
BY Day
etc. ...

Or you could use -

JOIN CLEAR *
JOIN Day IN HOLD1 TAG T1 TO Day IN HOLD2 TAG T2 AS J1
DEFINE FILE HOLD1
TOT_QTY/I9 = T1.QTY + T2.QTY;
END
TABLE FILE HOLD1
SUM TOT_QTY AS QTY
BY Day
ON TABLE HOLD AS HOLD3
END

and many more different methods, some better than others.

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
Member
posted Hide Post
What's the difference between the usual JOIN with JOIN CLEAR * ? Sorry, I'm already in the bus ride home, I can only try it tommorow Smiler

I already thought of SUMing between the two but, sorry, should've make this more clear, the combined table needs to be used next month as well.

June 1st, table 1:
Day     QTY
1       10


table 2:
Day     QTY
1       15
2       10
3       20
...
30      40
31      35


SUM between the two will result in
Day     QTY
1       25

when I needed the QTY to be 10. Any suggestion? thanks~


7.1.3 on XP into HTML, Excel, PDF
 
Posts: 27 | Registered: April 15, 2008Report This Post
Expert
posted Hide Post
Yuuta,

You should use Universal Concatenation or the MORE command.

TABLE FILE DAY1
SUM QTY
BY DAY
MORE
FILE DAY2
END


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
Presuming you want to report the highest of all quantities encountered for each date:

The report should sort the combined data BY DATE BY QTY then either PRINT the LST.DATE and LST.QTY ('LST.' prints the last instance) or SUM DATE and a DEFINEd field that's an alphabetic representation of QTY (SUM automatically prints the last instance of an alphabetic field in the sort's final BY group).


WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.
 
Posts: 154 | Location: NY | Registered: October 27, 2005Report This Post
Silver Member
posted Hide Post
This should work:

MERGE FILE TABLE1
SUM
QTY AS QTY1
BY DAY
RUN

FILE TABLE2
SUM
QTY AS QTY2
BY DAY
AFTER MATCH HOLD AS FINAL_TABLE OLD-OR-NEW
END
-RUN

DEFINE FILE FINAL_TABLE
QTY/D12.2 = QTY1 + QTY2
END

TABLE FILE FINAL_TABLE
SUM
QTY
BY DAY
END


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Virtuoso
posted Hide Post
I would go with Ginny's code - easiest and least amount of code, but as MORE is only valid for FOCUS tables, it would require that both initial tables are held in FOCUS format.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
Darin,

That is not true. MORE works for anything.


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
You are correct. Don't know why I had that stuck in my brain. Hmm. From the Manual:

With universal concatenation, you can retrieve data from unlike data sources in a single request; all data, regardless of source, appears to come from a single file. The MORE phrase can concatenate all types of data sources (such as, FOCUS, DB2, IMS, VSAM), provided they share corresponding fields with the same format. You can use WHERE and IF selection tests in conjunction with MORE. For related information, see Selecting Records for Your Report.

To use MORE, you must divide your request into:

One main request that retrieves the first data source and defines the data fields, sorting criteria, and output format for all data.

Subrequests that define the data sources and fields to be concatenated to the data of the main request. The fields printed and sorted by the main request must exist in each concatenated data source. If they do not, you must create them as virtual fields.

During retrieval, data is gathered from each data source in turn, then all data is sorted and the output formatted as specified in the main request.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
How about a match on the three files, using a
BY DAY
BY QTY
and hold old-or-new

MATCH FILE1
PRINT QTY
BY DAY
BY QTY
RUN
FILE2
PRINT QTY
BY DAY
BY QTY
RUN
FILE3
PRINT QTY
BY DAY
BY QTY
AFTER MATCH HOLD AS HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT DAY QTY
END


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
quote:
What's the difference between the usual JOIN with JOIN CLEAR *
Yuuta,

If you have to ask then it might be time to Read The FOCUS Manual?

As I mentioned earlier, there are many ways to achieve an end result, but you really need to analyse what occurs with your data when using each method and choose which one suits your purpose.

If you still have problems getting the result you need to achieve then perhaps you could explain in deeper detail. For instance if you want the result of QTY for Day 1 to be 10 then why are you summing in the first place and why don't you keep the original file?

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
Member
posted Hide Post
The manual? The manual's gone Razzer Once upon a time there's a manual when my company started using WebFocus but after a few years and a few personel changes the manual's just gone... haven't checked if there's any online version of the manual, just thought of that.

Ok, I'll try to be a more thoughrough this time, sorry all, English isn't my first language I have a hard time finding word to explain things Smiler

Actualy, after messing around with it some more I managed to get what I wanted. Took me 3 hold files changing and moving around values but it gets the job done. Haven't try out every single suggestion on this thread but I'll look into it maybe there's an easier solutions to my problem. Thank you all for your help.

On another note, rather than creating a new topic, I seem to be having some trouble with joining two hold files together. Both are created with ON TABLE HOLD AS FORMAT FOCUS and both join field are of the same format(A2). Yet it won't join >.<

My problem if anyone interested :
I got a table that changes value everyday, let's call it TBLLOG
QTY     NAME
10      A
15      B
20      C
25      D


I need to sum and compute etc the whole thing so it become's like this
TODAYDATE    A     B     C     D
15           10    15    20    25


then I need to create a graph on this daily including past dates of the month(starting from the 1st day of month up to today). I figured since the data on TBLLOG is changed everyday I'm going to need to store the whole data somewhere. That's when I thought of using a hold file for it. Let's call it TBLTEMPHOLD
DATE          A     B     C     D   
1             10    etc.
2             15
3             20
4             25
5             15
... up to
30            30
31            25


next month is the same thing all over again but rather than using a new hold file I'm thinking of reusing TBLTEMPHOLD by just changing the values to new values. Just changing, no calculations or anything. I hope this is clear, if there's any confusion just let me know, thanks~

This message has been edited. Last edited by: Yuuta,


7.1.3 on XP into HTML, Excel, PDF
 
Posts: 27 | Registered: April 15, 2008Report This Post
Master
posted Hide Post
The manuals are available online from tech support. Downloads are free, printed copies cost. To answer your question, JOIN CLEAR * clears all previous joins.


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
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders