Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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?