Comparing Strings
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21316
Printed Date: 06 May 2024 at 4:12am
Topic: Comparing Strings
Posted By: judylynn
Subject: Comparing Strings
Date Posted: 27 Jan 2015 at 9:46am
How would I compare the following strings (text fields)?
Crisis
Vulnerable
Stable
Safe
Thriving
I would like to display "Progressed" if someone's baseline increases in the Last Assessment field, and "regressed" if it decreases.
For example, If the baseline field is "Crisis" and the LastAssessment field is "Stable", the report would display "Progressed".
I have it working to display "Same" if there is no change, but I can't figure out the rest.
Is there some way to tell it that this is the order I want, so that it knows that "thriving" is greater than "Crisis" (for example)? Or is it some other function that would work?
|
Replies:
Posted By: adavis
Date Posted: 27 Jan 2015 at 10:21am
To get started, group your data on client ID or whatever, and organize your records by AssessmentDate desc.
Then, create a formula that defines a numeric value for each of your string values so you have something easy to use for comparison.
Create a new formula field @StateConversion
if {table.field} = 'Crisis' then 5 else
if {table.field} = 'Vulnerable' then 4 else
if {table.field} = 'Stable' then 3 else
if {table.field} = 'Safe' then 2 else
if {table.field} = 'Thriving' then 1
Then create another formula field @assessment
if {@stateconversion} < previous({@stateconversion}) then "Progressed" else
if {@stateconversion}> previous ({@stateconversion}) then "Regressed" else
if {@stateconversion}= previous({@stateconversion}) then "Same"
Drop the @assessment formula in your report. You will obviously have to modify this to fit your data, but you get the idea.
This is how I would go about it, but I am sure there are other more experienced people here who have cleaner ideas.
EDIT: note that if your records are sorted DESC you may have to use "next" in place of "previous" to get the formula to compare the correct record. You will have to play with it.
Also, you have a separate field for LastAssessment, you could use the same logic and create an additional numeric conversion formula for that field, then use the @assessment formula to compare them by replacing
if the next/previous(@stateconversion) portion of the formula with the @lastassessmentconversion formula.
|
Posted By: Vimal Nair
Date Posted: 27 Jan 2015 at 6:37pm
Concept mentioned above is good. But I would do the first level calculation at query level.
For Eg: Decode({table.field},'Crisis',5,'Vulnerable',4,'Stable',3,'Safe'2,'Thriving',1)StateConversion which will be more faster in retrieving the data.
And create the 2nd formula as mentioned above. But Sorting should be done mandatory as the formula depends on this sorted data.
-------------
Born To Live
|
Posted By: judylynn
Date Posted: 28 Jan 2015 at 4:21am
|
Thanks, I think I figured it out.
|
|