How to use if statement in dimension expression and return a list of values?

[YT1916]

Hi,

I want to create an expression for a dimension field to show different group of values within the dimension based on the field selections.

Below is the current expression I have to return only one value and it works properly. 

=If(GetFieldSelections(Trade) = 'A', 'A-1', Relation)

However, when I want to return 2 values, seems like the list of values does not work within the if statement.

=If(GetFieldSelections(Trade) = 'A', ['A-1' , 'A-2'], Relation)

Could anyone please help in this case? Much appreciate!

[Chanty4u]

Try this 

Pick(

    Match(GetFieldSelections(Trade), 'A'),

    'A-1',

    'A-1',

    'A-2',

    Relation)

[YT1916]

Hi! Thanks for the quick reply!

I tried it, but it only returns 'A-1', but still not showing 'A-2'.  : (

[Or]

An If() statement by definition returns only one value - either the True value or the False value, but never multiple values. Is there some sort of logic as to whether A-1 or A-2 need to be returned?

[YT1916]

I see. Thanks for the comments.

There's no logic behind the selected value. It's more like a simple grouped values within this 'Relation' dimension. 

For example:

If Trade 'A' is selected, I want the dimension 'Relation' in the pivot table only show 'A-1', 'A-2',

Trade	Relation	Week 1	Week 2
A	A-1	values of A-1, week 1	values of A-1, week 2
A	A-2	values of A-2, week 1	values of A-2, week 2

If Trade 'B' is selected, I want the dimension 'Relation' in the pivot table only show 'B-1', 'B-2', etc.

Trade	Relation	Week 1	Week 2
B	B-1	values of B-1, week 1	values of B-1, week 2
B	B-2	values of B-2, week 1	values of B-2, week 2

[vinieme12]

You don't need any conditional/calculated dimension for this

if your data is structured as you have shown above then the table will naturally only show relevant data

in your case when A is selected by default  A-1,A-2 will be filtered in the chart as well

unless the fields TRADE and RELATION are not associated with each other at a data model level !!

[vincent_ardiet_]

As @vinieme12 is mentioning, if [Relation] is in your datamodel this should be transparent.
Or, this field is in your application and you only want the 2 firsts only?
If this is the case, you can create a calculated dimension:

=Aggr(Only({<[Relation]={"*-1","*-2"}>} [Relation],[Relation])

[lissicanton]

In QlikView or Qlik Sense, the use of If statements within dimension expressions can be limited, especially when trying to return a list of values based on a condition. Instead, you can use set analysis and the aggr function to achieve your desired result.

Here's an example of how you might structure your expression:

=aggr(
if(GetFieldSelections(Trade) = 'A' and Match(Relation, 'A-1', 'A-2'), Relation),
Relation
)

In this example, the aggr function is used to aggregate the values of Relation based on the conditions specified within the if statement. The Match function is used to check if the Relation value matches either 'A-1' or 'A-2' when the Trade field is selected as 'A'.

This expression will return a list of values from the Relation field that meet the specified conditions. Adjust the conditions inside the if statement as needed for your specific requirements.

[YT1916]

Hi Vincent, thanks for the reply.

unfortunately the data between two fields are not associated, A can also be related to B-1, or C-3, etc. Meanwhile, A-1, A-2 are just sample names, there's no pattern in the Relation names. Maybe any possibilities to apply multiple ONLY() to pick up the Relations I want to show?

[vincent_ardiet_]

Difficult to help you without the data model and values. Could you at least create a sample dataset with dummy data so we can see the relationships?