Calculated value based on multiple rows

[RC987654321]

Hello, I believe this is a very simple question, but haven't been able to find a good solution:

I have a loaded resident table with two columns, a Product and the results of a Quality Test.

In the load script, I want to create a second table with the Product, and a calculated field that checks if any Quality Tests have value "Fail" then apply value "Bad", otherwise apply value "Good"

Resident Table:

Product	Quality Test
Product A	Pass
Product A	Fail
Product A	Pass
Product B	Pass
Product B	Pass
Product B	Pass

This is the secondary table I am trying to produce:

Product	Product Quality
Product A	Bad
Product B	Good

Thank you in advance for your help

[PrashantSangle · Accepted solution]

Hi,

use FindOneOf()

try below script
Test:
Load Product,
if(FindOneOf(Concat(DISTINCT Quality_Test),'F')>0,'Bad','Good') as Flag,
Concat(DISTINCT Quality_Test) as new_test_result
Inline [
Product, Quality_Test
Product A, Pass
Product A, Fail
Product A, Pass
Product B, Pass
Product B, Pass
Product B, Pass
]
Group By Product
;

Regards,
Prashant Sangle

[jonathandienst]

Chart or table with Product a dimension, and 

If(Count({<[Quality Test] = {'Fail'}>} Product) > 0, 'Bad', 'Good')

as a measure

[asinha1991]

There are two ways I can think of, both utilizing preceding load.

A. using concat..concat will aggregate all values for a product separated by delimiter. You can use something like this

Final Test:

Load Product, if(wildmatch(temp_agg,'*Fail*'),'Bad','Good') as [Product quality];

Load  Product, concat([Quality Test],',') as temp_agg

Resident Table group by Product;

B.  you can create a binary indicator, this is also helpful if you want to mark it bad if certain percentage of test fails.

//if any one test fails 

Final Test:

Load Product, if(sum(temp_ind)>0,'Bad','Good') as [Product Quality]

group by Product;

Load  Product, if(match([Quality Test],'Fail'),1,0) as temp_ind

Resident Table ;

//if more than 50% fails

Load Product, if(sum(temp_ind)/count(temp_ind)>0.5,'Bad','Good') as [Product Quality]

group by Product;

Load  Product, if(match([Quality Test],'Fail'),1,0) as temp_ind

Resident Table ;