topic Anything Similar to "Contain" function? in QlikView

Anything Similar to "Contain" function?

Fri, 03 Mar 2017 15:03:22 GMT

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!

Re: Anything Similar to "Contain" function?

sunny_talwar — Fri, 03 Mar 2017 16:31:16 GMT

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

Re: Anything Similar to "Contain" function?

Miguel_Angel_Baeyens — Fri, 03 Mar 2017 16:36:51 GMT

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

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

Re: Anything Similar to "Contain" function?

Fri, 03 Mar 2017 16:44:31 GMT

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?

Re: Anything Similar to "Contain" function?

sunny_talwar — Fri, 03 Mar 2017 16:49:11 GMT

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

Re: Anything Similar to "Contain" function?

Fri, 03 Mar 2017 17:08:33 GMT

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

Re: Anything Similar to "Contain" function?

sunny_talwar — Fri, 03 Mar 2017 17:37:17 GMT

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

Re: Anything Similar to "Contain" function?

Fri, 03 Mar 2017 17:41:21 GMT

Thank you Sunny!

Re: Anything Similar to "Contain" function?

MarcoWedel — Sun, 05 Mar 2017 22:28:09 GMT

maybe helpful:

Re: Load script find value from one table as substring

regards

Marco

Re: Anything Similar to "Contain" function?

rygreene3 — Fri, 02 Jul 2021 00:58:27 GMT

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