if [requirement] = 1
then if [on file] = 1 then "all good" else "missing" end
Are your tables joined?
Thank you for the reply Dmitry. I guess my problem is the requirement and the on file can be multiple for each customer for instance. requirement 1&2&... = 1(required) and on file is 1 or 0 for both or neither or just one.
I think Dmitry Chirkov's answer is correct, you just need to replicate it to cover conditions 1-6.
You can concatenate those results and determine if any failed:
Check For Fails
IF CONTAINS([Check 1]+[Check 2]+[Check 3]...,"missing") THEN "At least one check FAILED"
ELSE "All checks PASSED"
You might also be able to string these together into a single test:
Check For All Fails
[Requirement 1] = [Requirement 1] * [On File 1] AND
[Requirement 2] = [Requirement 2] * [On File 2] AND
[Requirement 3] = [Requirement 3] * [On File 3] AND
[Requirement 4] = [Requirement 4] * [On File 4] AND
[Requirement 5] = [Requirement 5] * [On File 5] AND
[Requirement 6] = [Requirement 6] * [On File 6]
Here I've done some fancy stringing of boolean tests. If a test is not required (0), it negates the results of the test and yields a TRUE for that requirement. And since I'm "adding" them using "AND", even one FALSE will make the entire result FALSE.
- You may need to convert dimensions into integers
- This also operates on the premise that every requirement and on file will be a 0 or a 1. If you have [On File] results that are null, you can use the ifnull function to reset them 0.
As Dmitri indicated, you might be able to do this more efficiently if your tables are joined. This might allow you to do a single LOD calculation for each customer.