Add text to recurring field

[liamlucas]

Most records in the “Clearance No” field recur.  If “Consign ID or Transport ID” fields have text in any record that “Clearance No” has a recurring value, then I want to enter the text ‘Confirmed’ into the “Rating” field.

Row No	Clearance No	Consign ID	Transport ID	Rating
1	1	CNSGN		Confirmed
2	1			Confirmed
3	2			Confirmed
4	2		TRNSPRT	Confirmed
5	3
6	3
7	3
8	4
9	5		TRNSPRT	Confirmed
10	5	CNSGN		Confirmed
11	6	CNSGN		Confirmed
12	7	CNSGN	TRNSPRT	Confirmed
13	7	CNSGN	TRNSPRT	Confirmed
14	7	CNSGN	TRNSPRT	Confirmed
15	7	CNSGN	TRNSPRT	Confirmed

I think this might be possible by incorporating the SUMPRODUCT function into the IF statement in the following load script, but I have not been able to figure it out, can you help?

[tblUpdate]:

Load

“Clearance No”,

“Consign ID”,

“Transport ID”,

 IF(NOT WILDMATCH([Consign ID],'* *')OR NOT WILDMATCH([Transport ID],'* *'),'Confirmed ')AS [Rating]

Resident [tblInvestigate]

[Vegar · Accepted solution]

ConfirmedRows:
Load
[Row No] as ConfirmedRow
Where
(Len([Consign ID])>0) OR
(Len([Transport ID])>0)
From Table ;

LOAD
[Row No],
[Clearance No],
[Consign ID],
[Transport ID],
If(exists( ConfirmedRow, [Row No]) , 'Confirmed' ) as Rating
FROM Table
;
Drop table ConfirmedRow;