Flag or Drop Records Based on Criteria

[MalcolmCICWF]

I have a strange circumstance here, I want to preferably flag and records with Status of  SC*, but only flag it if by Acct# it also doesn't have a Status of DM or EM. The Yellow highlighted example would not be flagged because it doesn't have a DM or EM by Acct#. The other two SCPST would be flagged or dropped because they have a record with DM or EM. 

[Vegar]

Maybe you could do something like this? 

LOAD distinct Acct# as Acct2Flag FROM Source WHERE Match(Status, 'DM', 'EM')

Load Acct#, Status, exists(Acct2Flag, Acct#) as Flag From Source;

[albertovarela]

Perhaps:

ConcatStatus:
LOAD Acct#, Concat(Distinct Status,',') as Statuses
Resident Data
Group By Acct#;

Left Join (YourTable) 
LOAD Acct#, 'SCPST' as Status, 1 as Flag //Replace the 1 with your value
Resident ConcatStatus
Where WildMatch(Statuses,'*SC*') and Wildmatch(Statuses,'*EC*','*DM*');

DROP Table ConcatStatus;

[MalcolmCICWF]

I'm not getting either of these to work great... how/where would I bring in multiple other fields I need for the report that I didnt list? Can I do that in the first LOAD? I ask because there are duplicate records and it would be hard to join those fields back afterwards.

[albertovarela]

Take a look at the attached qvf