Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Script and if statement

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

1 Solution

Accepted Solutions
jjfabian
Partner - Creator III
Partner - Creator III

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

View solution in original post

14 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

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
Partner - Creator III
Partner - Creator III

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


Not applicable
Author

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?

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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

Not applicable
Author

 

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.

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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