Dynamic Bookmarks with multiple selections for one field

[joboro]

Hi,

i try to implement a comfort feature for our user: For some users specific values should be pre-selected when they open the qlik sense app.

The dynamic bookmark looks promising for that, but I cannot get it to work with multiple values for one field.

The expression I am using is the following: 

=TheTable.TheField=PICK(match(Upper(SubField(OSUser(), '=', -1)),'GREATUSER'),'(BestValue_1|BestValue_2)')

But that doesn't give me any values back.

But the following expressions do work:

=TheTable.TheField=PICK(match(Upper(SubField(OSUser(), '=', -1)),'GREATUSER'),'BestValue_1')

=TheTable.TheField=PICK(match(Upper(SubField(OSUser(), '=', -1)),'GREATUSER'),'BestValue_2')

So the the selection criteria does not match somehow. Has anyone an idea how i could get this working?

KR

[vinieme12]

I would suggest you to simplify this

create a inline table in your data model

Load * inline [

USER,FilterValues

GREATUSER,BestValue1

GREATUSER,BestValue2

];

Then change bookmark expression to below

='('&concat( {<USER={"$(=Upper(SubField(OSUser(), '=', -1)))"}> }Distinct chr(39) & FilterValues & chr(39),'|')&')'

[joboro]

The expression works correctly and gives back ('BestValue_1'|'Best_Value_2') but when i use that expression for the selection nothing is found.

[joboro]

Any ideas why this isn't working? 

I have tried

='('&concat( {<USER={"$(=Upper(SubField(OSUser(), '=', -1)))"}> }Distinct chr(39) & FilterValues & chr(39),'|')&')'

=TheTable.TheField='('&concat( {<USER={"$(=Upper(SubField(OSUser(), '=', -1)))"}> }Distinct chr(39) & FilterValues & chr(39),'|')&')'

to filter the dimension "Arbeitsplatzgruppennr.".

[vinieme12]

just remove the preceding equal sign

=TheTable.TheField='('&concat( {<USER={"$(=Upper(SubField(OSUser(), '=', -1)))"}> }Distinct chr(39) & FilterValues & chr(39),'|')&')'

try below

TheTable.TheField='('&concat( {<USER={"$(=Upper(SubField(OSUser(), '=', -1)))"}> }Distinct chr(39) & FilterValues & chr(39),'|')&')'

OR

TheTable.TheField=concat( {<USER={"$(=Upper(SubField(OSUser(), '=', -1)))"}> }Distinct chr(39) & FilterValues & chr(39),'|')

[joboro]

Unfortunally no difference.  Tried several variants of that.

[joboro]

I tried again to use this expression in a sheet action to select the values in the field, but i get an error this time:

=$(=concat(Distinct chr(39) & FilterValues & chr(39),chr(124)))

=$(=concat(Distinct chr(39) & FilterValues & chr(39),'|'))

So it has something to do with the pipe i guess, but i cannot figure it out how i am supposed to build that expression to be working.

[joboro · Accepted solution]

What seems to be working is the following:

=TheTable.TheField=PICK(match(Upper(SubField(OSUser(), '=', -1)),'GREATUSER','GREATESTUSER'),
    only({<TheTable.TheField = {'BestValue_1','BestValue_2'}>}TheTable.TheField)
    , 'BestValue_1'
    )