Script and if statement

[#1]

Can someone tell me why this script wont work?

LOAD

UserName,
FirstName,
LastName,
Title,
OU,
Department,
Location,
Emailaddress,
WhenCreated,
PaswordLastChanged,
LastLogonTime,

AccControl,
Status,
PasswordAge,
if ('PasswordAge' >='60'<='180', RGB (255,0,0), RGB (0,0,0)) as PasswordFlag,
AccountLockout,
AccountExpirationDate,
NextPwdReset,
MFID,
Description,
Manager,
SSO,
"NON_Human";

SQL ...

[nilesh_gangurde]

Hi ethan,

You are using QlikView Functions for SQL query.

If you want different colors to analyse the data, do it at the front end.

Regards,

Nilesh Gangurde

[jjfabian]

Hi Ethan,

well, there's several things "wrong" with your script.

First of all, you need to refer to your actual Field, and not put it in high commas.

Same thing would go for your numbers.

Thirdly, your color definitions need to go into high commas so that you store them as text. You can't assign a color in script.

So your expression would have to look like this.

if (PasswordAge >=60<=180, 'RGB (255,0,0)', 'RGB (0,0,0))' as ...

However, I'm wondering what you are actually trying to achieve by doing this in your script.

Assigning colors should be done where you use your Field in an expression in your front-end.

Regards,

Jakob

[#4]

Ultimately what I'm trying to do is this..

I have a list of employee numbers (EmpNum) along with the age of their passwords (PassAge).

If their passwords (Password) are equal or over 60 days old and under 180 days old, I want to display their employee number (EmpNum) in yellow. If their password is older or equal too 180 days, I want to display their employee number in red.

Do I need to use a straight table to do this?

[#5]

Hi,

You can use straight table, add (EmpNum) in dimension and go to the text color and add the condition PassAge and select the RGB color according to it.

Regards,

Prasath

[jjfabian · Accepted solution]

Hi again,

yeah, a straight table should work for you.

- Add an only expression for PasswordAge -->only(PasswordAge)

- then add a conditional colors to either the expression background or text colour using nested ifs. Should look something like this:

if (only(PasswordAge)>=60, rgb(255,0,0),if(only(PasswordAge)<=180, rgb(0,0,0)))

You'll have to play around a little to get the colours and the behaviour right, but that should be how you do it in theory.

Regards,

Jakob

[#7]

That worked perfect as it sits. Thanks so much for the help. I really appreciate it!

[#8]

if

(only(PasswordAge)<=180, rgb(255,255,0),if(only(PasswordAge)>=180, rgb(255,0,0)))

Youre right about the behavior. It doesnt seem to let me put a range of numbers. ie >=60 <=180.

[#9]

I got it to work.

Could you tell me how to do the same thing but with dates?

So if i have a date MM/DD/YYYY, I want to find all dates that are between 180 and 60 days old.

Thanks

[jjfabian]

That should work in the same manner. So something like:

if(Date>'06/21/2012',rgb(255,0,0), if(Date<'06/25/2012',rgb(0,0,0)))

The high commas are the important part here, if you omit them nothing happens.

Now I understand you're looking at password ages, so you could take it to the next level - making the colour depend upon today's date using the today() function:

if(Date=>(today()-60),rgb(255,0,0), if(Date<=(today()-180),rgb(0,0,0)))

Again, you might have to play around here a little tofit your needs perfectly. Let me know if you need more help/ideas.

Best Regards,

Jakob