topic Re: Flag or Drop Records Based on Criteria in QlikView

Flag or Drop Records Based on Criteria

MalcolmCICWF — Sat, 16 Nov 2024 17:52:59 GMT

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.

Re: Flag or Drop Records Based on Criteria

Vegar — Tue, 03 Nov 2020 18:30:28 GMT

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;

Re: Flag or Drop Records Based on Criteria

albertovarela — Tue, 03 Nov 2020 18:55:35 GMT

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;

Re: Flag or Drop Records Based on Criteria

MalcolmCICWF — Tue, 03 Nov 2020 20:07:36 GMT

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.

Re: Flag or Drop Records Based on Criteria

albertovarela — Tue, 03 Nov 2020 20:51:22 GMT

Take a look at the attached qvf