Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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 ....;

View solution in original post

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.