topic Re: Using an expression to find if a variable value exists in a table field in App Development

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

neil-devlin — Tue, 28 Sep 2021 13:39:34 GMT

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.

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

stevejoyce — Tue, 28 Sep 2021 13:43:15 GMT

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;

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

neil-devlin — Tue, 28 Sep 2021 13:46:07 GMT

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().

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

stevejoyce — Tue, 28 Sep 2021 13:48:57 GMT

ok. so how about this then:

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

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

neil-devlin — Tue, 28 Sep 2021 14:06:57 GMT

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

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

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

stevejoyce — Tue, 28 Sep 2021 14:26:10 GMT

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)

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

neil-devlin — Tue, 28 Sep 2021 14:32:35 GMT

Brilliant, that did the trick! Thanks