Having some issues flagging a transaction

[#1]

I want to be able to identify reference numbers that include the [GL Acct] 200000

by creating a flag.

However if a reference number includes the GL Acct 200000, I want to flag the entire transaction (not just the line item that includes GL acct 200000)

so that i can see the other accounts that have the same reference number too (and apprear on the same transaction reference number).

For example see below:

Flagged

Reference #    GL Acct       Amount

818000           200000        200.00

818000           912000        -100.00

818000           812000        -100.00

Unflagged

Reference #    GL Acct       Amount

818009           310000        500.00

818009           814000        -300.00

818009           902440        -200.00

I have uploaded a QVD to see where i am running into problems and can not get the desired result

[#2]

Josep,

//Bigtable with all the data:

test:

  LOAD

  Trim(REFER) as Reference, 

Trim(ACT) AS [GL Acct],

AMTDOL as Amount,

[Field 1],[Field 2],[Field 3],[Field 4],[Field 5],[Field 6],[Field 7],[Field 8],[Field 9],[Field 10];

//tinytable to flag

test1:

noconcatenate

LOAD

[GL Acct],

     Reference,

   resident test ;

left join   load

Reference,

concat([GL Acct],'|') as Concat

resident test

group by Reference ;

Flaged:

noconcatenate

Reference,

if(wildmatch (Concat,'*|200000|*')>0,'Flag','Others') as FlagRef

resident test1;

drop table test1;

MAPFLAG:

mapping load Reference,FlagRef resident Flaged;

drop table Flaged

FinalBigTable:

applymap('MAPFLAG',Reference) as FlagREF;

load * resident test;

drop table test;

Untested.The idea is to solve the flag and then map the original table, so at the end you have all the columns of the begining andda new column that defines the flag.

Hope helps.

Sergio