Set expressions with If/Then logic

[benson_miller]

I have data that represents tests executed against a part, as shown:

TestData:
LOAD * Inline [
PartNumber, SerialNumber, TestType, TestSubType, TestResult

1234, abc, foo, fooA, PASS
1234, abc, foo, fooB, PASS
1234, abc, bar, barA, PASS
1234, abc, bar, barB, PASS

1234, bcd, bar, barC, PASS
1234, bcd, bar, barD, FAIL

1234, def, foo, fooA, FAIL
1234, def, foo, fooB, PASS
1234, def, bar, barB, PASS
1234, def, bar, barD, PASS

4567, efg, foo, fooZ, FAIL
4567, efg, foo, fooF, FAIL
4567, efg, bar, barF, FAIL
4567, efg, bar, barZ, FAIL
];

I want to create a table with columns as follows:

I'm having trouble with the column on the far right ("Passed FOO Testing?"). This column should report TRUE only if there are tests of type 'foo' for that part AND all of those tests resulted in 'PASS'.  Everything else should show FALSE in that column, including parts that have NO tests of type 'foo' and any parts that have one or more failed test of type 'foo'. 

I have been working primarily with set expressions to perform the aggregate count, which functions as expected, but it filters the table to show ONLY items that match both conditions. This is my current expression:

if(count({$<TestResult={'FAIL'},TestType={'foo'}>} TestResult)>0, 'FALSE', 'TRUE')

I'm not clear on the best way to combine the if/then logic to generate the 'TRUE' / 'FALSE' text values for the columns, with the logic to ascertain the TRUE/FALSE-ness. I appreciate your help.

[andoryuu · Accepted solution]

OK, so if i understand you correctly, ONLY "foo" lines where all "foo" tests for a specified PartNumber have PASS as the TestResult should be marked as "TRUE"  The following lines are false:

1. All non-foo Test Types
2. All TestResults = FAIL
3. All foo Test Types where any TestType=foo for the same PartNumber

If those hold true, this set analysis calc will work for you:

If(TestType='foo' and count({<PartNumber={"=count({<TestResult={'PASS'},TestType={'foo'}>}PartNumber)>0"}>-<PartNumber={"=count({<TestResult={'FAIL'},TestType={'foo'}>}PartNumber)>0"}>} TestResult)>0,'TRUE','FALSE')

Only foo lines are affected (if statement condition) but then you need to do an inner set analysis that returns only partnumbers where there are all PASS foo lines AND THEN remove the partnumber rows that have FAIL foo lines.  None of your original data met these criteria so in my screenshot you'll see that Partnumber 3737 only has PASS foo.

[benson_miller]

Thank you, andoryuu!

This is a great solution. I hadn't considered the removal of records as you have done. I made only one change to your expression to change the PartNumber reference to SerialNumber. As you rightly observed, there were no PartNumbers in my dataset that would register as 'Passed FOO testing', but there was one SerialNumber. Changing the fieldname gave me the desired result and taught me a few new tricks. Thanks again! 

Your expression with updated field name:

If(TestType='foo' and count({<SerialNumber={"=count({<TestResult={'PASS'},TestType={'foo'}>}SerialNumber)>0"}>-<SerialNumber={"=count({<TestResult={'FAIL'},TestType={'foo'}>}SerialNumber)>0"}>} TestResult)>0,'TRUE','FALSE')

[andoryuu]

Awesome!  Glad it has worked out.  Inner set analysis can be insanely powerful, but a pain in the butt to wrap one's head around :).