Return a value based on certain criteria

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!

Session ID to return value.xlsx

Help

Accepted answers

binu_acs

@binsell One way of doing this

return_value_21052024.yxmd

All comments

Rhys_Cooper

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

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.
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.
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.
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.
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
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.
Sort (ToolID="10"):
- Sorts the combined dataset by RecordID in ascending order to maintain the original order of the records.
Select (ToolID="11"):
- Selects the fields for the final output. In this case, it deselects the RecordID field and includes all other fields.

Screenshot 2024-05-21 124432.png Screenshot 2024-05-21 124456.png

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

fees.yxzp

binu_acs

@binsell One way of doing this

return_value_21052024.yxmd

binsell

@Rhys_Cooper thank you for taking the time - most helpful

binsell

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703