topic Calculated value based on multiple rows in QlikView

Calculated value based on multiple rows

RC987654321 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated value based on multiple rows

PrashantSangle — Wed, 10 Apr 2019 05:07:34 GMT

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

Re: Calculated value based on multiple rows

jonathandienst — Wed, 10 Apr 2019 05:29:07 GMT

Chart or table with Product a dimension, and

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

as a measure

Re: Calculated value based on multiple rows

asinha1991 — Wed, 10 Apr 2019 05:52:28 GMT

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 ;