Jump to content

Retrieve/get value from another record with multiple criteria


 Share

This topic is 4375 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi,

I just started using FM yesterday (I used to use MS Access) and I got stuck. I have a table with data on stocks (e.g., price, date, ticker). For example:

12/31/2010 AAPL 300.25

1/31/2011 AAPL 325.73

2/14/2011 AAPL 350.11

I have three questions.

1) If i'm setting up a new calculation field, how do I retrieve a value from a field within the same table and using two criteria? For example, for the 3rd record (2/14/2011) above, let's say I want to get the price value where date=12/31/2010 and ticker=aapl which would be 300.25. So, for the 3rd record, the field would show 300.25.

2) Continuing with question 1, let's say I can get that value (300.25) which shows up on the 3rd record and now I want to run a calculation of (price of 2/14 - the value we just got) / price of 2/14. This provides the % return of 2/14 from 12/31. What's the step to make this part happen?

3) Lastly, is this an inefficient way of setting up, organizing, or calculating data?

Thanks in advance.

JP

Link to comment
Share on other sites

how do I retrieve a value from a field within the same table and using two criteria?

Usually, by defining a self-join relationship using two pairs of matchfields.

is this an inefficient way of setting up, organizing, or calculating data?

Hard to say without knowing your purpose. Instinctively, I'd say you should have a parent table of Stocks and a child table of Prices, related by StockID or by TickerSymbol.

Link to comment
Share on other sites

Sorry, I'm having trouble understanding self-joins. I tried a tutorial, but I still don't seem to grasp the steps that I need.

Basically, i want to do the following:

table name:data

date ticker price value1

12/31/2010 AAPL 300.25

1/31/2011 AAPL 325.73 300.25

2/14/2011 AAPL 350.11 300.25

12/31/2010 CRM 115.22

1/31/2011 CRM 120.15 115.22

Where "value1" supplies the price for the related ticker for the date 12/31/2010.

Can you or someone else tell me the specific steps I need to take? Thanks again.

Link to comment
Share on other sites

my table has three fields:

Date

Ticker

Price

And its currently filled with all that data. With prices of various stocks at different dates. What I'd like to do is compare the price of a stock at one date to the price at another date. So, I was thinking I would create a new field to show/get the price of each stock for a specific date (e.g., 12/31/2010). So, If i'm looking at a record of AAPL at 1/31/2011 and it's price, then the last (new) field would show the price at 12/31/2010 (which would be pulled from another record).

Link to comment
Share on other sites

Yes, obviously you would need a second date field to hold the second date... but you haven't answered my question: are you picking this date arbitrarily or will it be calculated (e.g. end of previous year), and will it be the same for all records in the table - i.e. if you have picked 12/31/2010, then ALL prices will be compared to the price (of the same stock) on 12/31/2010?

Link to comment
Share on other sites

First, define a new field gDate (type Date, global storage).

Next, define a self-join relationship of the Prices table as:

Prices::Ticker = Prices 2::Ticker

AND

Prices::gDate = Prices 2::Date

Finally, define a calculation field (result is Number) =

( Price - Prices 2::Price ) / Price

In order to display the comparison price, just place the related field Prices 2::Price on your layout (make it non-enterable so that it cannot be modified accidentally).

Link to comment
Share on other sites

This topic is 4375 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.