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     Calculate two fields from different tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Calculate two fields from different tables
 Login/Join
 
<Mirza>
posted
Does anyone know of a technique to calculate fields from two tables without joining them? There is no common field within each table.

i.e. I'm would like to calculate (FIELD_A in Table 1) divided by (FIELD_B in Table 2)

Thanks.
 
Report This Post
susannah
Expert
posted Hide Post
Mirza, if there's no common field, how would you determine which value from of FIELD_B to use?
is it a constant?
if so, easy.
you read out that value into an &var and then use that &var in your calculations in table1.
TABLE FILE CAR
SUM SALES
ON TABLE HOLD AS SAVETHIS FORMAT ALPHA
END
-*CHECK FILE SAVETHIS
-RUN
-READ SAVETHIS &MYSALES.I6
...now you have an &VAR containing your table2 value and you can use this in defines and computes when you read table 1.
Make sure you know the length of your held field before you try to read it back in.
Thats why i threw that check command in there.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
<Mirza>
posted
Hi Susannah,

Sorry, I guess I should have clarified a bit further. The fields that I wish to calculate are summed fields.

For example TABLE 1 contains the total number of SALES only. TABLE 2 contains the total number of PRODUCTS only. There is no way to join the two tables because a common field does not exist in these tables.

I've tried your solution, but I get the parameter prompt for MYSALES. I think I understand what your telling me, in that insert the summed value in a parameter but I'm not sure how to do that, or call that parameter value.
 
Report This Post
Leah
Virtuoso
posted Hide Post
When you say total number of sales and total number of products, what other fields might exist. If is any relationship at all, you could pull all of the products sort by a 'caluclated/defined' field, pull all the sales sort by a 'calculated/defined' field. Then join the two to do your calculated/defined field if you can create a common calculated/defined field.
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
susannah
Expert
posted Hide Post
You can dummy out tht prompt
by -SET &MYSALES = 0 ;
before you run.
then
DEFINE FILE SOMEFILE
NEWVALUE/I8= &MYSALES / 2 ;
END
you can use this &var a zillion ways.
As Leah suggests, a compute is an option.
or you can even use it in a column header.
TABLE FILE SOMEFILE
PRINT SOMETHING AS 'value is &MYSALE '

(ps Tex's way, below, is the classic cool way, most flexible, imho)
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
TexasStingray
Master
posted Hide Post
if all you want is the total from each table here is an example using the car file and joining on a computed field.

TABLE FILE CAR
SUM RETAIL_COST
COMPUTE JOINKEY/A1 = ' ';
ON TABLE HOLD AS HOLD1
END

TABLE FILE CAR
SUM DEALER_COST
COMPUTE JOINKEY/A1 = ' ';
ON TABLE HOLD AS HOLD2
END

JOIN JOINKEY IN HOLD1 TO
JOINKEY IN HOLD2 AS J1
END

TABLE FILE HOLD1
PRINT
RETAIL_COST
DEALER_COST
JOINKEY
END

Hope this Helps
 
Posts: 865 | Registered: May 24, 2004Report This Post
<Mirza>
posted
Great! Thank you all for the help!
 
Report 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     Calculate two fields from different tables

Copyright © 1996-2020 Information Builders