topic Re: load only record with matched value in QlikView

load only record with matched value

Anonymous — Fri, 17 Feb 2017 19:13:32 GMT

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

Re: load only record with matched value

aarkay29 — Fri, 17 Feb 2017 19:19:15 GMT

Load*

From

Where

Match(Result,'NG');

Re: load only record with matched value

maxgro — Fri, 17 Feb 2017 19:21:51 GMT

And what about the result <> NG?

Keep or remove?

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;

Re: load only record with matched value

Anonymous — Fri, 17 Feb 2017 19:24:19 GMT

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

Re: load only record with matched value

aarkay29 — Fri, 17 Feb 2017 19:36:30 GMT

Try this expression in the script

If(ID=peek(ID),

If(Process=peek(Process),

If(Result='NG',Result),

Result),

Result) as Result;