Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Anything Similar to "Contain" function?

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

8 Replies
MVP
MVP

Re: Anything Similar to "Contain" function?

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

MVP
MVP

Re: Anything Similar to "Contain" function?

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

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

Not applicable

Re: Anything Similar to "Contain" function?

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?

MVP
MVP

Re: Anything Similar to "Contain" function?

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

Re: Anything Similar to "Contain" function?

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

MVP
MVP

Re: Anything Similar to "Contain" function?

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

Re: Anything Similar to "Contain" function?

Thank you Sunny!

Re: Anything Similar to "Contain" function?