Anything Similar to "Contain" function?

[#1]

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!

[sunny_talwar · Accepted solution]

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)

[#4]

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....

[#6]

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

[#8]

Thank you Sunny!

[MarcoWedel]

maybe helpful:

Re: Load script find value from one table as substring

regards

Marco

[rygreene3]

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