14 Replies Latest reply: Jun 27, 2012 8:58 AM by Jakob Fabian RSS

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

       

       

        • Re: Script and if statement
          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

          • Re: Script and if statement
            Jakob Fabian

            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


              • Re: Script and if statement

                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?

                  • Re: Script and if statement
                    Prasath Palani

                    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

                    • Re: Script and if statement
                      Jakob Fabian

                      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

                        • Re: Script and if statement

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

                          • Re: Script and if statement

                             

                            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.

                            • Re: Script and if statement

                              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

                                • Re: Script and if statement
                                  Jakob Fabian

                                  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

                                   


                                    • Re: Script and if statement

                                      I figured it out and used the today() variable.

                                       

                                      Now I'm trying to make a button. So that when its clicked it will display all my red flagged values, and another for all the yellow values.

                                        • Re: Script and if statement
                                          Jakob Fabian

                                          Well ...

                                          you should try to make the button using a variable.

                                          - Create a variable (I'd call it vPasswordAgeColor or something like that)

                                          - Tell your red button to set the variable's value to the actual formula you used for the red color in your expression (to do this, select the External -set variable action in your button or text object) - make sure you put the entire formula in high commas

                                          - do the thing vice versa for your yellow button

                                          - Lastly, add the =vPasswordAgeColor as Color in your expression ... now you can toggle your colors via these buttons.

                                           

                                          Alternatively, you could only put the color formula into the variable (eg. 'rgb(255,0,0)' )and replace them in your expression. But that's really a question of how you'd like to do this.

                                           

                                          I handle most of my colors in the load script in the beginning. I define all my colors for diagrams, tabs, borders, highlights as 'rgb(xxx,xxx,xxx)' values there so it's easy to change things globally and achieve a high level of consistency.

                                           

                                          Jakob

                                            • Re: Script and if statement

                                              So I just want to make sure I'm understanding this correctly.

                                               

                                              So if I made two variables, one for red and one for yellow. vPasswordAgeRed and vPasswordAgeYellow.

                                               

                                              Then used those variables in my nested if statements instead of actually putting the RGB (0,0,0) in it?

                                               

                                              So then when I used the button I can just have it due the external search and put in the variable and it should pull up wherever that variable is used?

                                               

                                              Thanks!

                                              • Re: Script and if statement

                                                I noticed you said you assign highlights in the script prior so that the highlights can be global.

                                                 

                                                So lets say I have an employee and their number is '123456789' and if their last logon time was over 120 days ago I want to flag them throughout the QVW no matter where they appear, can this be done? So that they're even flagged in table boxes where you cant use expressions.

                                                 

                                                Ultimately, I'm working on a security report. With multple tabs based on different applications and who has access to what applications.

                                                 

                                                I just need to make sure that a person who hasnt logged on is flagged everywhere in red.

                                                  • Re: Script and if statement
                                                    Jakob Fabian

                                                    Now that's something you probably can't do - at least I wouldn't know how to.

                                                     

                                                    What I meant by global colors is adding them in the script as variables so you never have to think about them again - Example: Imagine you use a certain red color everywhere (fontcolors, headers, backgrounds, bar colors and so on) in your QVW --> vColor1. If you wanted to change to another color and used an rgb() function everywhere, well... have fun changing your rgb function everywhere. However, if you used vColor1 everywhere, all you would have to do is change your variable in the script ONCE.

                                                     

                                                    So while you can't tag a field with a certain color you can at least ensure consistency by assigning the same color wherever you need it.