| Author |
Message |
judylynn
Newbie
Joined: 17 Nov 2008
Location: United States
Online Status: Offline
Posts: 22
|
   Topic: Comparing Strings
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?
|
 IP Logged |
|
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
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.
Edited by adavis - 27 Jan 2015 at 10:26am
|
|
IP Logged |
|
Vimal Nair
Newbie
Joined: 29 Dec 2014
Location: United Arab Emirates
Online Status: Offline
Posts: 26
|
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
|
|
IP Logged |
|
judylynn
Newbie
Joined: 17 Nov 2008
Location: United States
Online Status: Offline
Posts: 22
|
Posted: 28 Jan 2015 at 4:21am |
|
Thanks, I think I figured it out.
|
|
IP Logged |
|
|
|
