Return a value based on certain criteria

[binsell]

Hi all - I have attached my data what it looks like before and what I want it to look like after. In the column 'proportion of fees 23/24' I wanted to return a value based on the 'SCSESSIONID' column. The logic I want to apply as follows:

IF 'SCSSESSIONID' has characters that DON'T contain an underscore AND contains a value in 'Proportion of fees 22/23' THEN return the value in 'PRORTION OF FEES 22/23' into 'PRORTION OF FEES  Value 23/24'

IF 'SCSESSIONID' has an underscore in the characters AND contains a value in 'Proportion of fees 22/23' then do the following calculation: SCSFEE (gross) MINUS 'Proportion of fees 22/23' and return this value in 'PRORTION OF FEES  Value 23/24'

I hope that makes sense and thank you in advance for your help. This community is brilliant! 

[Rhys_Cooper]

Hi @binsell, thank you for your question. Please find my steps outlined below, the results and the workflow attatched

1. Record ID (ToolID="9"):

   • Adds a RecordID field to each row starting from 1. This field helps to uniquely identify each row to perseve the order by sorting ascending at the end.

2. Filter (ToolID="3"):

   • Filters rows based on the condition !Contains([SCSESSIONID], "_") and !IsEmpty([Proportion of fees value 22/23]). This is your first logic condition stated.
   • If the condition is True, the row proceeds to the next filter step.
   • If the condition is False, the row goes to another branch for additional processing based of the second logic condition you provided.

3. Formula (ToolID="4"):

   • For rows that meet the first filter condition, creates a new field PRORTION OF FEES Value 23/24 with the same value as Proportion of fees value 22/23.

4. Filter (ToolID="6"):

   • For rows that didn't meet the first filter condition, applies another filter: Contains([SCSESSIONID], "_") and !IsEmpty([Proportion of fees value 22/23]).
   • If the condition is True, the row proceeds to a formula step.
   • If the condition is False, the row is directed to the union tool without modification.

5. Formula (ToolID="7"):

   • For rows that meet the second filter condition, creates a new field PRORTION OF FEES Value 23/24 with the value SCSFEE (gross) - [Proportion of fees value 22/23]. as per your stated formula

6. Union (ToolID="8"):

   • Combines the outputs from the formula tools and any rows that did not meet either filter condition.
   • The union is performed by field names.

7. Sort (ToolID="10"):

   • Sorts the combined dataset by RecordID in ascending order to maintain the original order of the records.

8. Select (ToolID="11"):

   • Selects the fields for the final output. In this case, it deselects the RecordID field and includes all other fields.

any questions or modifications please let me know - hope this helps! - Rhys Cooper

[binuacs · Accepted solution]

@binsell One way of doing this

[binsell]

@Rhys_Cooper  thank you for taking the time - most helpful :) 

[binsell]

@binuacs Thank you as always for your solutions, I think you reply to most of my questions! I love this community!