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