Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Violent_Democracy
Contributor II
Contributor II

Exclude output where data starts with a letter

Hi there,

I am trying to produce a report in Qliksense and I have a column called Facility Reference. In this column there are 6 digit codes which are numeric and that identify a property, but there are also properties that start with B, G and S.

How can I filter out all the properties that start with B,G and S in the expression?

Thanks in advance.

3 Replies
techno-list
Partner - Contributor II
Partner - Contributor II

Create flag by taking first letter of the field. if first letter matches with 'B' or 'S' or 'G' then 0 else 1.

you can use the flag in set analysis {<flag={1}>}.

Base:
Load * Inline [
Facility_Reference
123456
S12345
234567
223344
G23456
987654
B78910
543210
];

Temp:
Load
Facility_Reference,
if(WildMatch(left(Facility_Reference,1),'B','G','S'),0,1) as Flag
Resident Base;

Vegar
MVP
MVP

You can exclude [Facility Reference] values starting with B,G, and S in an expression using SET analysis.

Assuming you want to calculate SUM(Amount) then you can adjust that expression to the following

=SUM({<[Facility Reference] -= {"B*", "G*", "S*"} >}Amount)

Fonds
Contributor II
Contributor II

Can you do it on dimensions too?