Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try this
Pick(
Match(GetFieldSelections(Trade), 'A'),
'A-1',
'A-1',
'A-2',
Relation)
Hi! Thanks for the quick reply!
I tried it, but it only returns 'A-1', but still not showing 'A-2'. : (
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?
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 |
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 !!
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])
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.
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?
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?