Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
neil-devlin
Contributor III
Contributor III

Using an expression to find if a variable value exists in a table field

Hopefull this makes sense. I want to set a variable value to 1 or 0 based of if another variable value is found in a table. For example if I have a variable set to a username how do I then set my other variable to 1 or 0 based on whether that username exists in a table of usernames I have loaded in?

in laymans terms

if table contains username then set another variable to 1.

1 Solution

Accepted Solutions
stevejoyce
Specialist II
Specialist II

put single quotes around.  and make sure vUser starts with an = sign.

=if(count({<UID={'$(vUser)'}>} UID)>0,1,0)

 

if it doesn't begin with equal sign try this:

=if(count({<UID={'$(=$(vUser))'}>} UID)>0,1,0)

 

View solution in original post

6 Replies
stevejoyce
Specialist II
Specialist II

I may not understand correctly but you can load a 2nd table with a where clause and then count number of rows.

let vUserName = 'steve';

 

data:

load * inline [

username

joe

steve

chris

]

;

 

check_steve_exists:

load

1 as steve_exists

resident data

where username = '$(vUserName )';

 

let vSteveExists = if(NoOfRows('check_steve_exists')>0, 1, 0);

 

drop table check_steve_exists;

 

neil-devlin
Contributor III
Contributor III
Author

Hey thanks but I need to do this in an expression to set a variable rather than when I am doing the intial load script as I cant set the first variable (username) until the cube is opened and I can determine it via OSUSER().

stevejoyce
Specialist II
Specialist II

ok.  so how about this then:

=count({<username = {'$(=osuser())'} >} username)

neil-devlin
Contributor III
Contributor III
Author

Unfortunately if I integrate this into my expression the variable just becomes NULL,

 

=if(count({<UID={$(vUser)}>} UID)>0,1,0)

stevejoyce
Specialist II
Specialist II

put single quotes around.  and make sure vUser starts with an = sign.

=if(count({<UID={'$(vUser)'}>} UID)>0,1,0)

 

if it doesn't begin with equal sign try this:

=if(count({<UID={'$(=$(vUser))'}>} UID)>0,1,0)

 

neil-devlin
Contributor III
Contributor III
Author

Brilliant, that did the trick! Thanks