AnsweredAssumed Answered

How to account for records that may be empty/not exist

Question asked by trialuser1111 on Jan 14, 2010
Latest reply on Jan 14, 2010 by trialuser1111

Title

How to account for records that may be empty/not exist

Post

I'm struggling with a calculation in my database and there must be a solution to my problem but I have not been able to figure it out by myself.  Essentially, the calculation takes a stock price from one table and multiplies it by the shares held in another table.  Here's the catch: the shares held may be a positive integer, it may be zero, it may be blank, or there may be no record at all.  The relationship for this calculation is based on the stock ticker and the companyID for the holding firm.  If the firm has never held a position in the ticker, there will be no matching record.

 

What I want my calculation to do is substitute an a zero for the shares held if it's anything other than a positive integer.  I have tried the following, and neither has been able to account for the possibility of a non-record, I get a ? in my resulting field:

 

Lookup (Market Data 3::Last Price) * Case ( IsValid (Holdings Prio::Q3'09) ; Holdings Prio::Q3'09 ; 0 ) / 1000000

Lookup (Market Data 3::Last Price) * ( If ( IsValid ( Holdings::Q3'09 ) ; If ( IsEmpty (Holdings::Q3'09) ; 0; Holdings::Q3'09 ) ; 0 ) / 1000000 )

Outcomes