Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Anything Similar to "Contain" function?

Hi, I am currently trying to be able to create a calculated field in which I can filter whether or not an observation contains a string of characters.

ie:
Fruits Purchased
Bananas, Apples, Oranges
Bananas, Apples, Kiwis, Lemons

Apples, Pears, Oranges

I would like to only show observations that include at least one of the following: Apples, Oranges.

Is there a function that can do something similar in the script?

Thank you very much for taking a look!

1 Solution

Accepted Solutions
sunny_talwar

You just want to flag those rows where Apple or Orange appears? May be use WildMatch

LOAD [Fruits Purchased],

           If(WildMatch([Fruits Purchased], '*Apples*', '*Oranges*'), 1, 0) as Flag

           ....

From ....;

View solution in original post

9 Replies
sunny_talwar

You just want to flag those rows where Apple or Orange appears? May be use WildMatch

LOAD [Fruits Purchased],

           If(WildMatch([Fruits Purchased], '*Apples*', '*Oranges*'), 1, 0) as Flag

           ....

From ....;

Miguel_Angel_Baeyens

Elaborating a bit on Sunny's script, but in the expression:

Sum({< [Fruits Purchased] = {"*Apples*", "*Oranges*"} >} Amount)

Not applicable
Author

Hi Sunny and Miguel, 

Thanks for the replies! Yes, I would like to flag those rows where apple and orange appears so that I can perform analysis on multiple other columns but only for apples and oranges. However, I am in Qlik Sense, not Qlik View(my bad I didn't mention), and I dont think flag is a function there. Is there another way I can do this?

sunny_talwar

The function we have used is WildMatch and not Flag... You can use the same script that I provided or expression that Miguel provided to do this in Qlik Sense also....

Not applicable
Author

Hi Sunny,

My apologies, this is my first day using Qlik. I have used your script.

if(WildMatch([Fruits Purchased], '*Apples*', '*Oranges*'), 1,0)

Can I use this to return all values of the rest columns(TotalAmount, PurchaseDate, StoreLocation) ?

Thanks again,

Bradley

sunny_talwar

I hope that you have done this in your script, right?

LOAD [Fruits Purchased],

           if(WildMatch([Fruits Purchased], '*Apples*', '*Oranges*'), 1,0) as Flag

          Amount,

          StoreLocation,

          PurchaseDate

FROM ...

Now create a table chart with dimensions

StoreLocation

PurchaseDate

[Fruits Purchased]

Measure

Sum({<Flag = {1}>}Amount)

Here {<Flag = {1}>} is a set analysis syntax which will tell your chart to show only those rows where Flag = 1 and hide any other row from the chart.

You can read more about set analysis here:

A Primer on Set Analysis

Not applicable
Author

Thank you Sunny!

MarcoWedel

rygreene3
Contributor II
Contributor II

Thank you Sunny!  Your explanation saved the day.  Really appreciate it.