14 Replies Latest reply: May 16, 2012 12:40 AM by Ravindra Babu Gudimallam RSS

    Dynamic qualification

    Ravindra Babu Gudimallam

      Dear All,

       

      Is anything wrong in the below qualification.

       

      customqual = (('Assigned Group' = "HQ-CHQ SAP Auth Admin") OR ('Assigned Group' = "SS-Shared Serv. SAP Auth Admin")) AND (('Status' = "Resolved") OR ('Status' = "Closed")) AND (('Last Resolved Date'  >= ("5/14/2012 12:00:00 AM " -864000) AND  'Last Resolved Date'  <= "5/14/2012 12:00:00 AM ") OR ('Closed Date'  >= ("5/14/2012 12:00:00 AM " -864000) AND  'Closed Date'  <="5/14/2012 12:00:00 AM "))

       

      I doubt in the higlighted part. Please suggest.

      I am trying get the records resolved/closed past 10 days...

       

      Thanks & Regards

      Ravindra

        • 1. Dynamic qualification
          Yogini Rajemane

          Hi Ravindra,

           

          Instead of directly using "5/14/201212:00:00 AM" in qualification make use of either DATE or SERVERTIMESTAMP keyword as per your requirement.

           

          Regards,

          Yogini

          • 2. Dynamic qualification
            Ashwanth Padmanaban

            try like this

             

            'Last Resolved Date'  >= ("5/14/2012 12:00:00 AM -864000")

             

            or use 'Last Resolved Date'>=("$TIMESTAMP$-864000")

            • 3. Dynamic qualification
              Carl Wilson

              Hi,

              as the Date/Time fields are stored in epoch time, you cannot just reference a "visible" date format to perform calculations on, you can only use these for an exact Date/Time match.

               

              If you are after determining a calculation for the past 10 days, then you can make use of the $TIMESTAMP$ keyword to give you the current Date/Time and subtract the equivalent 10 days (864000) from this.

               

              E.g. 'Last Resolved Date' >= $TIMESTAMP$ - (((60*60) * 24) * 10)

               

              Cheers

              Carl

               

              http://www.missingpiecessoftware.com/

              • 4. Dynamic qualification
                Rajesh Nair

                the bracebrackets are incomplete

                 

                 

                 

                 

                Try this

                 

                ('Status' >= "Resolved") AND ((('Last Resolved Date'  >= "5/14/2012 12:00:00 AM " -864000) AND ('Last Resolved Date'  <= "5/14/2012 12:00:00 AM ")) OR (('Closed Date'  >= "5/14/2012 12:00:00 AM " -864000) AND  ('Closed Date'  <="5/14/2012 12:00:00 AM ")))

                • 5. Dynamic qualification
                  Ravindra Babu Gudimallam

                  Thanks all.

                   

                  I modified the qualification to

                   

                  ((((((((((((((((((("customqual = " + $z1D Qual$) + " AND (('Last Resolved Date'  >= (") + """") + $TIMESTAMP$) + " -864000)") + """") + " AND  'Last Resolved Date'  <=") + """") + $TIMESTAMP$) + """") + ") OR ('Closed Date'  >= (") + """") + $TIMESTAMP$) + " -864000)") + """") + " AND  'Closed Date'  <=") + """") + $TIMESTAMP$) + """") + "))"

                   

                  and the result is

                   

                  customqual = (('Assigned Group' = "HQ-CHQ SAP Auth Admin") OR ('Assigned Group' = "SS-Shared Serv. SAP Auth Admin")) AND (('Status' = "Resolved") OR ('Status' = "Closed")) AND (('Last Resolved Date'  >= ("5/14/2012 3:32:13 PM -864000)" AND  'Last Resolved Date'  <="5/14/2012 3:32:13 PM") OR ('Closed Date'  >= ("5/14/2012 3:32:13 PM -864000)" AND  'Closed Date'  <="5/14/2012 3:32:13 PM"))

                   

                  And I am doing SET FIELD to one Data visualization field on button.Still I am not getting the flashboard filled.

                   

                  For the same Flashboard, I used the below qualification in flashboard varaible. It worked fine.

                   

                  (('Assigned Group' = "HQ-CHQ SAP AuthAdmin") OR ('Assigned Group' = "SS-Shared Serv. SAP AuthAdmin")) AND (('Status' = "Resolved") OR ('Status' = "Closed")) AND((('Last Resolved Date' >= ($DATE$ -864000)) AND ('Last Resolved Date' <= $DATE$)) OR (('ClosedDate' >= ($DATE$ - 864000)) AND ('Closed Date' <= $DATE$)))

                   

                  Please suggest.

                  • 6. Dynamic qualification
                    Ravindra Babu Gudimallam

                    Here is the updated qualification

                     

                     

                    ((((((((((((((((((("customqual = " + $z1D Qual$) + " AND ((('Last Resolved Date'  >= (") + """") + $TIMESTAMP$) + " -864000") + """") + ")) AND  ('Last Resolved Date'  <=") + """") + $TIMESTAMP$) + """") + ")) OR (('Closed Date'  >= (") + """") + $TIMESTAMP$) + " -864000") + """") + ")) AND  ('Closed Date'  <=") + """") + $TIMESTAMP$) + """") + ")))"

                     

                     

                    Result as:

                     

                     

                    customqual = (('Assigned Group' = "HQ-CHQ SAP Auth Admin") OR ('Assigned Group' = "SS-Shared Serv. SAP Auth Admin")) AND (('Status' = "Resolved") OR ('Status' = "Closed")) AND ((('Last Resolved Date'  >= ("5/14/2012 3:57:55 PM -864000")) AND  ('Last Resolved Date'  <="5/14/2012 3:57:55 PM")) OR (('Closed Date'  >= ("5/14/2012 3:57:55 PM -864000")) AND  ('Closed Date'  <="5/14/2012 3:57:55 PM")))

                     

                     

                    Still No Flashboard.

                    • 7. Dynamic qualification
                      Rajesh Nair

                      Are you get any error message when running the qualification

                       

                      ("customqual = " + ($z1D Qual$)) + " AND (((((((((((((((((((('Last Resolved Date'  >= (") + """") + $TIMESTAMP$) + " -864000)") + """") + " AND  'Last Resolved Date'  <=") + """") + $TIMESTAMP$) + """") + ") OR ('Closed Date'  >= (") + """") + $TIMESTAMP$) + " -864000)") + """") + " AND  'Closed Date'  <=") + """") + $TIMESTAMP$) + """") + "))"

                       

                      the brace bracket is the issue here

                       

                      not sure if this work.. But i stongly feel the placing of the brace bracket is an issue.

                       

                      I am not an guru here.:)

                      • 8. Dynamic qualification
                        Carl Wilson

                        Hi,

                        the brackets will be inserted by the Tool dependant on the version.

                        Is the 'Last Resolved Date' field a Date only or Date/Time field?  This will determine which keyword will work i.e. $DATE$ or $TIMESTAMP$ and also the value to subtract as both these fields are stored differently in the database.

                         

                        Cheers

                        Carl

                         

                        http://www.missingpiecessoftware.com/

                        • 9. Dynamic qualification
                          Ashwanth Padmanaban

                          when there are no errors it would be tough to debug... you gotta go with each query, execute it and then append one more and execute and check and thats how you can trouble shoot this...

                           

                          for example:

                          first go with

                           

                          (('Assigned Group' = "HQ-CHQ SAP Auth Admin") OR ('Assigned Group' = "SS-Shared Serv. SAP Auth Admin")) AND (('Status' = "Resolved") OR ('Status' = "Closed"))

                           

                          execute this and check

                           

                          then if that works then add on another statement and check

                          (('Assigned Group' = "HQ-CHQ SAP Auth Admin") OR ('Assigned Group' = "SS-Shared Serv. SAP Auth Admin")) AND (('Status' = "Resolved") OR ('Status' = "Closed")) AND (('Last Resolved Date'  >= ("5/14/2012 3:57:55 PM -864000")) AND  ('Last Resolved Date'  <="5/14/2012 3:57:55 PM"))

                           

                          you can drill down which field is causing the issue.. which one is having data mismatch, syntax mismatch or logical relationship error or if the query is syntatically right but it might not find any value from the backend form...

                           


                          • 10. Dynamic qualification
                            Ravindra Babu Gudimallam

                            Last Resolved Date & Closed Date are "Date/Time" Data type. (OOB Field)

                             

                            1. I checked with both DATE & TIMESTAMP

                             

                            For Date, ((('Last Resolved Date'  >= ("5/15/2012 -864000")) AND  ('Last Resolved Date'  <="5/15/2012")) OR (('Closed Date'  >= ("5/15/2012 -864000")) AND  ('Closed Date'  <="5/15/2012")))

                             

                            For TIMESTAMP, ((('Last Resolved Date'  >= ("5/14/2012 3:57:55 PM -864000")) AND  ('Last Resolved Date'  <="5/14/2012 3:57:55 PM")) OR (('Closed Date'  >= ("5/14/2012 3:57:55 PM -864000")) AND  ('Closed Date'  <="5/14/2012 3:57:55 PM")))

                             

                            Is Bold portion is correct?

                             

                             

                            FYI,

                            1. The first time I opened the form, Flashboard is working fine with qualification in Flashboard variable.

                            2. I have 2 fields , Start Date and End Date. With these dates filled, I am passing customqual by clicking on button. This is also working fine

                            3. The next one is Refresh. 

                            3.1 With start date and end dates filled, Refresh is working fine.. ( again same customqual)

                            3.2 With NO dates, not working.

                             

                            Thanks & regards

                             


                            • 11. Dynamic qualification
                              Rajesh Nair

                              one suggestion

                               

                              why don't write 2 different qualification 1 with date != null and other with null and put it in a guide and check.

                               

                              more over i would like to know the requirement of using -864000 in the qualification.

                               

                              YOU just need date which resolved or closed between dates right

                              • 12. Dynamic qualification
                                Ravindra Babu Gudimallam

                                I am already calling AL Guide on Refresh button.

                                 

                                 

                                1. If StartDate & End Date fields are ! $NULL$ , bulding one dyanamic customqual= .  It is working fine.

                                  Flasboard with incident details resolved/closed between those dates.

                                 

                                ((((((((((((((((("customqual = " + $z1D Qual$) + " AND (('Last Resolved Date'  >=") + """") + $Start Date$) + """") + " AND  'Last Resolved Date'  <=") + """") + $End Date$) + """") + ") OR ('Closed Date'  >=") + """") + $Start Date$) + """") + " AND  'Closed Date'  <=") + """") + $End Date$) + """") + "))"

                                 

                                2. If StartDate & End Date fields are $NULL$, buildign another dyanamic customqual= . This is not working.

                                Flashboard with Incident details resolved/closed for past 10 days.

                                 

                                ((((((((((((((((((("customqual = " + $z1D Qual$) + " AND ((('Last Resolved Date'  >= (") + """") + $DATE$) + " -864000") + """") + ")) AND  ('Last Resolved Date'  <=") + """") + $DATE$) + """") + ")) OR (('Closed Date'  >= (") + """") + $DATE$) + " -864000") + """") + ")) AND  ('Closed Date'  <=") + """") + $DATE$) + """") + ")))"

                                 

                                Is the below is correct and returns value?

                                ('Last Resolved Date'  >= ("5/14/2012 12:00:00 AM -864000")


                                Thanks & regards

                                • 13. Dynamic qualification
                                  Ravindra Babu Gudimallam

                                  Also, I notcied below

                                   

                                  once a wrong qualification passed to data visualization field, even if you pass Correct/working qualification to same data visualization field later, flashboard is not working.

                                   

                                  I mean,

                                   

                                  Step 1. when openeing the form first time, I have a default flashboard - working fine

                                  Step 2. If I pass working dynamic qualification - working fine

                                  Step 3. If I pass not working dynamic qualification - flashoard is not working.

                                  Step 4. If I pass the working dynammic qualification which i used in step 2 , - flashoard is not working.

                                   

                                  any comments on this?

                                   

                                  Regards

                                  Ravindra

                                  • 14. Dynamic qualification
                                    Ravindra Babu Gudimallam

                                    Hello All,

                                     

                                    Finally , Qualificatin worked.

                                     

                                    1.Temp_Start_Date = $DATE$

                                    2.Temp_End_Date = $DATE$-864000

                                     

                                    Then,

                                     

                                    ((((((((((((((((("customqual = " + $z1D Qual$) + " AND (('Last Resolved Date'  <=") + """") + $Temp_Start_Date$) + """") + " AND  'Last Resolved Date'  >=") + """") + $Temp_End_Date$) + """") + ") OR ('Closed Date'  <=") + """") + $Temp_Start_Date$) + """") + " AND  'Closed Date'  >=") + """") + $Temp_End_Date$) + """") + "))"

                                     

                                    Also, there was some mistahe in > & < earlier..

                                     

                                    Thanks all for your inputs.

                                     

                                    Regards

                                    Ravindra