load only record with matched value

[Anonymous]

Hello

If I have a table

ID	Process	Result
1234	SMT1	NG
1234	SMT1	OK
5645	SMT2	OK
9235	SMI1	OK
6224	SMT2	OK
5645	SMT1	OK
6224	SMT1	OO

For a group of records with same ID and process, I want to keep only the record which has result= NG.

For example in the above table, for ID =1234 and process=SMT1, there are two records with different result.

I just want to keep the only record which ID=1234, process=SMT1 and result=NG.

How can I do it?

Thanks,

Josh

[aarkay29]

Load*

From

     DB

Where

     Match(Result,'NG');

[maxgro · Accepted solution]

And what about the result <> NG?

Keep or remove?

Q:

LOAD ID,

     Process,

     Result

FROM

[https://community.qlik.com/thread/250389]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Q)

load ID, Process, sum(if(Result='NG', 1, 0)) as NGCount

Resident Q

group by ID, Process;

QQ:

NoConcatenate load *

Resident Q

Where (NGCount=1 and Result='NG') or NGCount=0;

DROP Table Q;

[Anonymous]

but, that will filter out all other records.

I want the table become:

ID	Process	Result
1234	SMT1	NG
5645	SMT2	OK
5645	SMT1	OK
6224	SMT2	OK
6224	SMT1	OO
9235	SMI1	OK

[aarkay29]

Try this expression in the script

If(ID=peek(ID),

    If(Process=peek(Process),

          If(Result='NG',Result),

    Result),

Result) as Result;