topic Re: Data filtering in expression in Connectivity & Data Prep

Data filtering in expression

victor_jacquens — Fri, 10 Dec 2021 21:09:00 GMT

Hello,

I have an app where I keep track of the stock of some products, and a few of them have a limit on their stock.

I want to create a dimension that only keeps data for these limited products, but I have a problem when writing the expression. I want to keep the 'Class 1' products with category '2' or '3' and the 'Class 5' products with category '3' since they are the only one with a stock limit.

Here's my expression:

=if( [Product Class] = 'Class 1' and [Product Category] = ('2','3'), 'Class 1 Cat.2&3',
if( [Product Class] = 'Class 5' and [Product Category] = '3', 'Class 5 Cat.3'))

The filtering on Class 5 is working fine but with Class 1, I can't seem to filter on 2 different categories.

What should I modify in my expression?

Re: Data filtering in expression

GaryGiles — Fri, 02 Apr 2021 10:23:38 GMT

There are a couple of ways to approach this. One is to use the Match() function, like this:

=if( [Product Class] = 'Class 1' and Match([Product Category],'2','3')>0, 'Class 1 Cat.2&3',
if( [Product Class] = 'Class 5' and [Product Category] = '3', 'Class 5 Cat.3'))

Basically, the Match() will return the position of the [Product Category] value in the list provided (in this case '2,'3') and returns 0 if it is not found.

Re: Data filtering in expression

victor_jacquens — Fri, 02 Apr 2021 10:28:27 GMT

Thank you,

I just found another solution, which is:

=if( ([Product Class] = 'Class 1' and [Product Category] = '2') or ([Product Class] = 'Class 1' and [Product Category] = '3'), 'Class 1 Cat.2&3',
if( [Product Class] = 'Class 5' and [Product Category] = '3', 'Class 5 Cat.3'))

Re: Data filtering in expression

GaryGiles — Fri, 02 Apr 2021 10:32:02 GMT

Like I said, there are multiple approaches. The Match() function approach has the advantage of being easier to read/write/change, especially if you had many [Product Class] values that you wanted to include.