Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

Flag or Drop Records Based on Criteria

 

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. 

 

Capture.PNG

Labels (10)
4 Replies
Vegar
MVP
MVP

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
Partner - Specialist
Partner - Specialist

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
Creator III
Creator III
Author

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
Partner - Specialist
Partner - Specialist

Take a look at the attached qvf

 

2020-11-03_10-57-24.png