PICK and MATCH

[ss09]

2 questions - does Pick and Match have an else condition? I have tried the +1 method didn't work.

My current expression:

Pick(

Match('$(vOneSpecialtyDemand)','All cars','suv','sedan','offroad'),

    SUM({<demand_occupation_id_psd_demin={101,102,103}>} visits_per_year_psd),

    SUM({<psd_spec_group_key={101}>} visits_per_year_psd),

    SUM({< psd_spec_group_key={102}>} visits_per_year_psd),

    SUM({<psd_spec_group_key={103}>} visits_per_year_psd),

    SUM({<specialty_name_psd_demin={'$(vOneSpecialtyDemand)'}>}visits_per_year_psd)

  ) 

Note: $(vOneSpecialtyDemand) - Are individual cars

Each sum expression works by itself individually. When it hits the else condition it shows this 

And when the condition is met - for All cars, the rows with 0 don't disappear?

[Or]

Typically the easiest way to handle this is to use Pick with WildMatch. WildMatch(Field,'Something','Something Else','*') means the third string match will pick up everything as an "Else" (excluding nulls, if you have those you'll need to use Coalesce).

Note that there's no reason for rows to disappear just because this particular expression returns zero. It looks like other measures in the same row do return values, meaning the row should be displayed even if show zero/null is disabled.

[vincent_ardiet_]

If the last one is the "else" expressions, you should write it like this:

Pick(

1+Match('$(vOneSpecialtyDemand)','All cars','suv','sedan','offroad'),

    SUM({<specialty_name_psd_demin={'$(vOneSpecialtyDemand)'}>}visits_per_year_psd),

    SUM({<demand_occupation_id_psd_demin={101,102,103}>} visits_per_year_psd),

    SUM({<psd_spec_group_key={101}>} visits_per_year_psd),

    SUM({< psd_spec_group_key={102}>} visits_per_year_psd),

    SUM({<psd_spec_group_key={103}>} visits_per_year_psd)

  ) 

[ss09]

Another example:
If I am doing a Pick and Match where my set expression takes part of a dataset.
Table has 10rows for example. Match 1 takes all 10, Match 2 takes 1-3, Match 3 takes 4-6, Match 4 takes 7-10 rows. How does one go about doing that? I don't want to see the rows with zeros.

I want to give the user the flexibility to choose to visualise data from either level 1 or level 2. If the user chooses a higher level grouping the filter should do it on level_1 - only show me level 1.

	Level 1	Level 2	price
2019	101	1	100
2019	101	2	203
2019	101	3	34
2019	102	4	234
2019	102	5	621
2019	102	6	900
2019	103	7	234
2019	103	8	11
2019	103	9	340
2019	103	10	1000

[vincent_ardiet_]

Sorry but I don't understand your problem.

[marcus_sommer]

Seeing zero-results within an object isn't related to the pick(match()) or if it's a simple sum(Field) else to the available data-set, the used data-model and the other dimensions + expressions within the object. This means you may need to adjust the data-set and/or data-model and/or implementing appropriate conditions within all respectively the relevant dimensions/expressions. Further this zero's or NULL results are not mandatory wrong or a sign of a bad design else it might be showing rather the truth as suppressing them.

Beside of this I wouldn't use a pick(match()) for such a scenario else just providing the user appropriate field-values for the selection. For example instead of using a variable the group_key could be associated with a car-type description. In the same way could be numeric levels be grouped whereby a simple <= or >= on the list-box should be more sensible. Another way for grouping-logic even with overlapping entries could be The As-Of Table - Qlik Community - 1466130.

[ss09]

An Else condition would be if the user selects a level 2 condition. Example: The user can either select sedan, where he will see all rows of sedans (bmw m3, honda civic.. n cars). Or the user can select "Honda civic" and get only one row value

[vincent_ardiet_]

But have you tried my expression?
This is what this is supposed to do.
If Match returns 0 meaning that user's choice is different than 'All cars', 'suv', 'sedan' or 'offroad', Pick will take the first choice which will apply <specialty_name_psd_demin={'$(vOneSpecialtyDemand)'}>

[Aasir]

A calculated Measure first
IF(Match('$(vOneSpecialtyDemand)','All cars','suv','sedan','offroad') > 0,
Pick(
Match('$(vOneSpecialtyDemand)','All cars','suv','sedan','offroad'),
SUM({<demand_occupation_id_psd_demin={101,102,103}>} visits_per_year_psd),
SUM({<psd_spec_group_key={101}>} visits_per_year_psd),
SUM({< psd_spec_group_key={102}>} visits_per_year_psd),
SUM({<psd_spec_group_key={103}>} visits_per_year_psd),
SUM({<specialty_name_psd_demin={'$(vOneSpecialtyDemand)'}>}visits_per_year_psd)
),
0 )

if you want to filter out rows with 0 values when "All cars" is selected
Sum({<your_dimensions_here, visits_per_year_psd = {">0"}>} YourMeasure)