Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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.
Take a look at the attached qvf