How to: search value in row then update all related records (different column) based on KEY/ID?

[QlikV1]

Hi, how can I search for value 'Reject' (during QVD load) in column DESCR and if TRUE update STATUS to 'FAIL' for all records with same ID? Please see below table.

Also, I was thinking about using wildmatch, however, this would only update a single record:

If(WildMatch(DESCR, '*Reject*'), 'FAIL', STATUS) as STATUS

Current data set:                                                                Objective:

ID	STATUS	DESCR			ID	STATUS	DESCR
aa	OK	x			aa	OK	x
aa	OK	x			aa	OK	x
bb	OK	x			bb	FAIL	x
bb	OK	Reject			bb	FAIL	Reject
cc	OK	x			cc	OK	x

IF 'Reject' is found in column DESCR then change STATUS to 'FAIL' for all rows with the same ID (in this case BB)

[Taoufiq_Zarra · Accepted solution]

@QlikV1  Maybe like :

Input_QVD:

LOAD * INLINE [
    ID, STATUS, DESCR
    aa, OK, x
    aa, OK, x
    bb, OK, x
    bb, OK, Reject
    cc, OK, x
];
left join 
load ID,MinString(DESCR) as NewStatus resident Input_QVD group by ID;


Final:

load ID,if(NewStatus='Reject','FAIL',STATUS) as STATUS,DESCR resident Input_QVD;

drop table Input_QVD;

output:

[QlikV1]

Thank you for your prompt feedback, works like a charm : )