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

[neil-devlin]

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.

[stevejoyce]

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]

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]

ok.  so how about this then:

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

[neil-devlin]

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

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

[stevejoyce · Accepted solution]

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]

Brilliant, that did the trick! Thanks