Set hard filter for table, independent from selected value

[DutchArjo]

I have a worksheet with multiple tables.

example:

The tables on the bottom have to be filtered by the filter 'team'.

the table on the top has to be filtered on a hard value, independent of what is selected in the filter in the blue square.

I created a filter for the upper table with the expression:

if(Team='Staf', Groep,)

which worked.

But then I created the lower 2 tables which do have to be filtered by the selected value in the blue square (only 1 selected possible). However, this changes the table on the top also.

I then thought: I create an alternate state for the table on the top so the selection in 'Team' doesn't affect this table, but I ended up with 'Team' being in the selection bar, showing 'selection in 2 states'.

I do not want selections in 2 states. I only want selections in 1 state.

How can I achieve this? Is it possible to have the top table filtered on a hard coded value and not being affected by any selection made in this variable AND the variable not having 'selections in 2 states' in the active selections bar? I want to be able to quickly change the selection of 'team' as I expect the user not to understand for which state the selection has to be changed.

[marcus_sommer]

I suggest to rethink the entire approach because duplicating the tables is rather not a suitable way else probably a painful detour. I assume that connecting or even merging the team and team-member tables is more expedient and should allow all needed views.

[DutchArjo]

Hello Marcus,

Thank you for your reply and your help.

I am sure Qlik data architects or Qlik Specialists might start somewhat different. However, the situation is the client wants this 'tree-view'.

Giving them an easy overview about open positions per team. there is only 1 team 'staf' which hasnt any groups. The other teams all have multiple group. Therefore I was looking for a way to have the staf table fixed on team 'staf' so I doesnt changes when other teams are selected.

it's some sort of (interactive) organigram but then build in Qlik (at least, thats the objective).

[marcus_sommer]

It's possible to derive n object-views from a single table in the data-model - side by side and reacting and/or ignoring certain/all selections with the use of appropriate set analysis, like 1 as set identifier and/or listing the wanted fields with something like:

maxstring({1} TeamMember) 

or 

maxstring({< Field1, Field2 >} TeamMember)

It may not very trivial but using n tables and duplicated fields and/or alternate states won't be easier to implement else being the opposite.

[DutchArjo]

I used the load script now to load the data for 'staf' in a separate staf-table, filtering the loaded data in the load script using the where statement:

where [team] = 'Staf'

I now only have to adjust the other table to make sure the 'staf' data is not loaded.

[marcus_sommer]

This could be reversed with:

where [team] <> 'Staf'

but if there are no further differences in the data-structures both loads will be loaded in the same table. By adding a noconcatenate statement to the second load the auto-concatenation could be avoided. But in this case you will get a lot of synthetic keys between the tables - which could be prevented with an appropriate re-naming of the fields.

This approach won't save any efforts to create the UI objects and to apply an appropriate set analysis to control their behaviour in regard to any selections - else this is the basic work. The handling of the duplicated fields and synchronizing the selections between them is then an on-top complexity.

[DutchArjo]

I renamed the fieldnames in the load script.No synthetic keys here so fare, just 3 separate tables for know.

It perhaps is not the best working solution, but for now, it is enough for me. I just need it to be able to show how the colleagues could be using the qlik dashboard instead of recording everything in an Excel which is full of macro's which do not work anymore and which the colleagues not understand.

[DutchArjo]

I continued to add a different table to the model and to get rid of the "duplicate"  tables in the load script.

However, I don't see/understand how I can filter the data/table based on a fixed value using set analysis expressions.

When I use the suggestiod expression (=maxstring({1} TeamMember) ) and adjust it: nothing happens.

So far, I only have set expressions with an sum or count function which actually count the number of times something is in the data matching the giving criterium. But I don't see/understand how I can do such a thing without using a sum or count or whatever just to filter the data.So I hope you can explain this to me.

the dimension is 'peloton'  and the value which should be used as filter value in the set analysis expression is 'Staf LBO'.

when I use this expression:

=sum({1<[peloton]={'$(=MaxString([peloton]))'}>}[gp nieuwe ehd])

but.. there is nothing to sum...

I wonder how (and where) I should enter the set analysis expression to only get all the records that have Staf LBO as peloton value.

[marcus_sommer]

Maxstring() was used because it looked that always a single string should be returned and for the case there isn't a single one else several not to fail with NULL like only() would do. The {1} was just an example like the other one of {< Field1, Field2 >} to show possibilities to ignore selections and it may need some adjustments to your scenario.

Beside this you will always have and need aggregations to show anything within the UI - especially if the objects should not (completely) follow the global selection state - and aggregations are the only possibility to define another selection state by adjusting any selections and/or ignoring them. 

[DutchArjo]

Thank you for explaining.

In this case, =sum({1<[peloton]={'$(=MaxString([peloton]))'}>}[gp nieuwe ehd]),

somehow, it does get the correct string value to filter on:

But, the result is invalid dimension. I cannot understand how to filter without having to use a calculation like sum or count in the set expression.

(I'm already surprised it came up with staf lbo as value, or perhaps it's just coincedence. since there are enough other values, even other text values).