Forum Discussion
IF statement with multiple columns and logical tests
Hi all,
I’m trying to make a column with values from several other columns:
If Validation Result OET = “obligatory test” and Validation Date OET = blank, new column should say: “obligatory test”.
In the same column, I would like to have:
If Days to DET Validation Deadline < 0, and Validation Date DET = blank, “Not Timely”
If Days to OET Validation Deadline < 0, and Validation Date OET = blank, “Not Timely”
If Days to DET Validation Deadline < 0, and =< 30, and Validation Date DET = blank, “0-30”
If Days to OET Validation Deadline < 0, and =< 30, and Validation Date OET = blank, “0-30”
If Days to DET Validation Deadline < 30, and =< 60, and Validation Date DET = blank, “30-60”
If Days to OET Validation Deadline < 30, and =< 60, and Validation Date OET = blank, “30-60”
Else, “over 60”
All in all, the new column should be giving me either:
- Obligatory test
- Not Timely
- 0-30
- 30-60
- Over 60
Is it possible to put this in 1 string, and therefore in one column, or is it better to approach this in a completely different way?
There are several other statuses in the “Validation Result DET” and “Validation Result OET” columns.
It is possible to have a finding which has “Effective” as a DET result and “Obligatory test” as OET result, so I figured I can’t just filter out all DET Effectives for example. Therefore I think I need to put it in one IF statement…
Does anybody know what’s best in this matter?
So something like the following?
Column = SWITCH( TRUE(), [Validation Result OET] = “obligatory test” && ISBLANK([Validation Date OET]), "obligatory test", [Days to DET Validation Deadline] < 0 && ISBLANK([Validation Date DET]), “Not Timely”, [Days to OET Validation Deadline] < 0 && ISBLANK([Validation Date OET]), “Not Timely”, [Days to DET Validation Deadline] <= 30 && ISBLANK([Validation Date DET]), “0-30”, [Days to OET Validation Deadline] <= 30 && ISBLANK([Validation Date OET]), “0-30”, [Days to DET Validation Deadline] > 30 && [Days to DET Validation Deadline] <= 60 && ISBLANK(Validation Date DET), "30-60", [Days to DET Validation Deadline] > 30 && [Days to DET Validation Deadline] <= 60 && ISBLANK([Validation Date OET]), “30-60”, “over 60” )Warning, probably a few syntax errors hiding in there but this should provide the concept.
3 Replies
So something like the following?
Column = SWITCH( TRUE(), [Validation Result OET] = “obligatory test” && ISBLANK([Validation Date OET]), "obligatory test", [Days to DET Validation Deadline] < 0 && ISBLANK([Validation Date DET]), “Not Timely”, [Days to OET Validation Deadline] < 0 && ISBLANK([Validation Date OET]), “Not Timely”, [Days to DET Validation Deadline] <= 30 && ISBLANK([Validation Date DET]), “0-30”, [Days to OET Validation Deadline] <= 30 && ISBLANK([Validation Date OET]), “0-30”, [Days to DET Validation Deadline] > 30 && [Days to DET Validation Deadline] <= 60 && ISBLANK(Validation Date DET), "30-60", [Days to DET Validation Deadline] > 30 && [Days to DET Validation Deadline] <= 60 && ISBLANK([Validation Date OET]), “30-60”, “over 60” )Warning, probably a few syntax errors hiding in there but this should provide the concept.
Thanks for your help!
This does exactly what I want it to do.
The solution did indeed give some syntax errors but they are easily fixed.
Thanks a lot!
