Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Partner
Partner

Match function

Hello, I am trying to use match function but the result is not what I expected. I am using this

If(Match(name, 'X', 'Y'),
Sum([Cost])
)

When I try to select the "X" name the result sum show the sum cost of "X" and the same result if I select "Y". But if I select "X" and "Y" the result will show as "-" I also change the expression to

If(name = 'X' and name = 'Y'),
Sum([Cost])
)

If(name = 'X' or name = 'Y'),
Sum([Cost])
)

The result still the same. Could you help me?

Any advice will be greatly appreciated

1 Solution

Accepted Solutions
MVP
MVP

Re: Match function

The expression using [name] without an aggregation function (known as a 'naked field name') can only return a value if there is only one possible value for [name]. Otherwise it will return null. The behaviour of a naked field is the same as one wrapped in Only(...).

If you want to sum for 'X' or 'Y', but want to respect user selections as well, then you could use something like one of these:

=Sum({<name *= {'X', 'Y'}>} Cost)    // intersection of user selections and 'X', 'Y' selection
or
=Sum({<name += {'X', 'Y'}>} Cost)    // union of user selections and 'X', 'Y' selection

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
8 Replies
Channa
Valued Contributor III

Re: Match function

Use wildmatch

Match returns true false case

Channa
Partner
Partner

Re: Match function

Thank you for your advice but after I try the result still the same.

If(wildmatch(name, 'X, 'Y'),
Sum([Cost])
)

If I selected only X or Y the result will show but if I selected X and Y the result will be - again

Channa
Valued Contributor III

Re: Match function

Ur missing single cote on top of X

'X' 

Channa
Partner
Partner

Re: Match function

Oh, thanks but after I fix the expression it still the same. If select two variables the result will not show.

Channa
Valued Contributor III

Re: Match function

May you have null values
Check data
Channa
Partner
Partner

Re: Match function

Hi!

It all depends where you use it.

If you create a UI table with name as dimension, then your formula works. It also works in Load script, because the comparison happens for every row. If you try to use it without dimension, then you need to use aggregation function for name eg 

If(Match(MaxString(name), 'X', 'Y'),
Sum([Cost])
)

Regards,
Sergey
MVP
MVP

Re: Match function

The expression using [name] without an aggregation function (known as a 'naked field name') can only return a value if there is only one possible value for [name]. Otherwise it will return null. The behaviour of a naked field is the same as one wrapped in Only(...).

If you want to sum for 'X' or 'Y', but want to respect user selections as well, then you could use something like one of these:

=Sum({<name *= {'X', 'Y'}>} Cost)    // intersection of user selections and 'X', 'Y' selection
or
=Sum({<name += {'X', 'Y'}>} Cost)    // union of user selections and 'X', 'Y' selection

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Match function

Many thanks to all of you for good advice.