How to flag on base of below criteria of data

[DipeshVadgama]

Hi All,

I want to flag data based on criteria. Please find attach file for reference.

[OmarBenSalem]

Table:
LOAD
trim(SubField(Data,'|',1)&SubField(Data,'|',-1)) as Key,
PurgeChar(Data,' ') as Data,
"Count 1",
"Scenario 1"
FROM [lib://DATA_SOURCES/Need Help.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
load *,if(Data like '*|RR|AC|*' and Key = Previous(Key) and Previous(Data) like '*|AC|*',0,

if(Data like '*|RR|AC|*' and Key = Previous(Key) and Previous(Data) like '*|ASC|*',-1,

if(Data like '*|ASC|*',1,

if(Data like '*|AC|*' and Previous(Data) <> '*|ASC|*',0 ))))
as NewCount

Resident Table Order by Key;

drop Table Table;

Result:

[DipeshVadgama]

Thanks, But I want to add in below code. How to do that ?

Table:

LOAD

SubField(Data,'|',1) as Num,

SubField(Data,'|',SubStringCount(Data,'|')+1) as Ref,

SubField(Data,'|',1)&SubField(Data,'|',SubStringCount(Data,'|')+1) as Group,

Data

FROM [lib://downloads/Need Help.xlsx]

(ooxml, embedded labels, table is Sheet1) ;

NoConcatenate

Table1:

load *, RowNo() as row, if(Index(Data,' | RR | BS | ' )<>0,1

,

if(Index(Data,' | RR | AC | ' )<>0 and Group=previous(Group),-1

)

)

as Index1 Resident Table Order by Group;

drop table Table;

NoConcatenate

Final:

load * , if (len(Index1)=0 and len(Index2)=0,'',if(len(Index1)=0,Index2,Index1)) as Count ;

load *,if(Index(Data,' | RR | AC | ' )<>0 and Group=previous(Group),0)  as Index2

Resident Table1 order by row desc;

drop table Table1;

drop Fields row,Index1,Index2 from Final;