• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

Skipping fields to perform record comparisons

My dilemma:

I have a SQL database of point-in-time "patient assessments".  (Relevant variables: PatientID, AssessmentDate, HasCancer)  Assessments are completed at any date in the year, but the only ones that are valid for my purposes are the last assessments in each quarter.  I need to compare each assessment to its "prior" valid assessment - ie. the last assessment performed in the previous quarter for the same patient.  As multiple assessments could be performed in a single quarter, I occasionally need to skip records and perform comparisons on records occurring 2 or 3 assessments back.

The only operators I am familiar with that can retrieve prior records for comparison are the Previous({fieldName}) and Next({fieldName}) operators.  For example,

if ( Previous({@quarter}) = {@quarter} - 1) then Valid = 1 else Valid = 0;

The Previous operator only looks at the previous record.  If the previous record is an invalid assessment (in same quarter), I am left paralyzed to performing comparisons with assessments before the previous.

As you can see, the "Valid" variable is a formula created within the database, at run time, so I cannot simply perform a Select to remove invalid fields.  Essentially, the validity criteria must be calculated only after my database is pulled into Crystal (from what I can tell).  Is there a workaround that can let me skip "Invalid" fields to perform comparisons?
Example

if( PriorValidAssessment.{HasCancer} = false and {HasCancer} = true ) then PatientDevelopedCancerLastQuarter = true;

Thanks for your help.
0
johnstonf
Asked:
johnstonf
  • 3
  • 2
  • 2
2 Solutions
 
frodomanCommented:
I'm thinking that you want to use a variable to store the information and then compare based on that.

shared booleanVar LastQuarter;
shared booleanVar ThisQuarter;
booleanVar PatientDeveloped := false;
// If next quarter is different this is the last record of this quarter
if {@quarter} <> next({@quarter}) then
(
   // Copy current data to last quarter variable
   LastQuarter := ThisQuarter;
   // Populate current data
   ThisQuarter := {YourTable.HasCancer};
   // Compare current to prior
   if LastQuarter = False and ThisQuarter = True then  
      ( PatientDeveloped := true; )
);
PatientDeveloped;
0
 
OutinCommented:
@frodoman:

Nice solution!
I would probably have done the same thing, but in 2 or 3 formulas....this one is a beauty :-)

--
Outin
0
 
johnstonfAuthor Commented:
Agreed! Just working on implementing it....now for a follow up question.

Do you think it is possible to produce a list of patients who have developed the disease?  Ie. Select patients based on the result this runtime formula....if PatientDeveloped = True then print.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
OutinCommented:
You can conditionally suppress section(s) based on the formula, or (maybe) use it in your selection criteria....

--
Outin
0
 
frodomanCommented:
Yes, you probably can develop a list of patients, but that's an entirely different process and should probably be opened in a new thread.  

Simply put you'll need to create an array that gets redimed and appended every time a new record is found that you want to add to the list, then you'll later need to loop through the array to print the results.

frodoman
0
 
johnstonfAuthor Commented:
After a bit of research on my part, Outin's conditonal suppress suggestion worked beautifully on test data.  I will be implementing both solutions over this week.  Thank you both.
0
 
OutinCommented:
Glad it works...

--
Outin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now