14 Replies Latest reply: Dec 13, 2017 4:29 PM by Diwas Karki RSS

    Max function for date is not working

    Diwas Karki

      Hello , my data in a multibox looks like this:

       

      inactive.png

       

      I have multiple rows for same office code( South Central and pacific northwest) with different access date but  I need to bring in the latest date.  I tried using a trigger "on-open" for this multibox and added this expression "Max(Num#(Access_Date))"  but its not working. When I tried to use that max function in the script itself. It brought in all the access_date separately( meaning only the access dates were displayed in the box in the multi box after all the data were displayed up top ).

      Can anyone suggest how to use the trigger feature correctly or help me write the correct script?  I would appreciate the help. Thanks

        • Re: Max function for date is not working
          Stefan Wühl

          Try loading your dates using the Timestamp#() or Date#() function with an appropriate format code, like

           

          LOAD

               Timestamp#(Mid(Access_Date,6,16)&Right(Access_Date,4),'MMM DD hh:mm:ss YYYY') as Access_Date,

               ...

           

          Why don’t my dates work?

           

          Then you should be able to use Max() function on your dates (now having a dual value and a numeric representation).

            • Re: Max function for date is not working
              Diwas Karki

              Hello Stefan, Thanks for replying.

               

              I tried that and I used Max(Access_Date) As Access_Date. This gave me a script error.

              This is what my script looks like:

               

              SPT_AUDIT_EVENT:

              Completed_Date,
              Timestamp#(Mid(Completed_Date,6,16)&Right(Completed_Date,4),'MMM DD hh:mm:ss YYYY')as Access_Date,

              FROM [..\..\QVD\SPT_AUDIT_EVENT.qvd] (qvd);

               

               

               

               

              SPT_AUDIT_EVENT2:

              Max(Access_Date) As Access_Date,

              RESIDENT SPT_AUDIT_EVENT;

               

              I got a script error on SPT_AUDIT_EVENT2. Any ideas?

              I appreciate your help

               

               

                • Re: Max function for date is not working
                  Stefan Wühl

                  What do you want to achieve with your second table?

                   

                  I believe you shouldn't use the comma after Access_Date in your SPT_AUDIT_EVENT2 tabe load.

                    • Re: Max function for date is not working
                      Diwas Karki

                      I have the second table so I can use the max function on the access_date.  I didn't know how to use time stamp and max function at the same time.

                        • Re: Max function for date is not working
                          Stefan Wühl

                          That's ok if you want to aggregate your timestamps to find the max value.

                           

                          Remove the comma after the last field name:

                           

                          SPT_AUDIT_EVENT2:

                          Max(Access_Date) As Max_Access_Date // renamed field for clarity and removed the comma

                          RESIDENT SPT_AUDIT_EVENT;

                            • Re: Max function for date is not working
                              Diwas Karki

                              this is my complete table, the comma was there as I just copy pasted . I am getting a script error with what you suggested.

                              script error.png

                                • Re: Max function for date is not working
                                  Stefan Wühl

                                  Well, that's because you Need to put all field names that are not used in an aggregation function in a GROUP BY clause. See the HELP for details.

                                    • Re: Max function for date is not working
                                      Diwas Karki

                                      Hello Stefan,

                                      I can now run the script you provided with no errors . But there is a slight problem.

                                      Data now is displayed as "Jul 29 06:16:22 2016" but if the day is a single digit it will bring in the first letter of the time zone like this ("Jul 9 06:16:22 C2016").

                                      My original format looks like this (Fri, Jul 29 06:16:22 CDT 2016).

                                      Any idea how  I can eliminate "C"? I really appreciate your time.

                                      Below is a picture:

                                      IMG_1136.jpg

                                        • Re: Max function for date is not working
                                          Stefan Wühl

                                          maybe something like

                                           

                                          Timestamp#(

                                              Subfield(Access_Date,' ',2) &' '& Subfield(Acces_Date,' ',3) &' '& Subfield(Access_Date,' ',4) &' '& Subfield(Access_Date,' ',6)

                                               ,'MMM D hh:mm:ss YYYY') as Access_Date



                                            • Re: Max function for date is not working
                                              Diwas Karki

                                              Hello Stefan,

                                              This script is working for me :

                                               

                                              load date(date#(if(not isnull(Completed_Date),mid(Completed_Date,6,index(Completed_Date,' ',3)-6)&' '&right(Completed_Date,4)),'MMM DD YYYY'),'MM/DD/YYYY') as Access_Date

                                               

                                              I have extract scripts that bring data from our database and creates qvd tables. We have a dashboard and have dashboard extract scripts which brings fields from the qvd tables. Those fields are displayed in the dashboard. The script I mentioned is in the the database extract script.

                                              In the dashboard extract script

                                              I am loading the Access_Date field in SPT_AUDIT_EVENT TABLE which is corrleated with INACTIVE_AUTOFIRE_USERS_REPORT table.

                                              when I try to use max function in the same script where Im loading the date it gives me a script error.

                                              So I tried bringing in the date field in a different table( say audit_Table2) with a resident load. Max function runs fine.

                                              But the problem is Im having a hard time correlating audit_Table2 with INACTIVE_AUTOFIRE_USERS_REPORT table .  I know I am sounding very confusing right now.

                                              I need to find a way to run the max function in this script:

                                              SPT_AUDIT_EVENT:
                                              LOAD ID As AuditID,
                                              MODIFIED As AuditModified,
                                              ACTION As AuditAction,
                                              APPLICATION As AuditApp,
                                              INSTANCE As AuditInstance,
                                              Upper(TARGET & '.' & APPLICATION) As ManagedAttributeAuditKey3,
                                              AuditLCMRequestID,
                                              AuditAutoFireRequestee,
                                              AuditAutoFireRequestID,
                                              AuditAutoFireOffices,
                                              AuditLCMCompletionDate,
                                              //date(date#(if(not isnull(CompletedDate),mid(CompletedDate,6,index(CompletedDate,' ',3)-6)&' '&right(CompletedDate,4)),'MMM DD YYYY'),'MM/DD/YYYY') as Completed_Date,
                                              Completed_Date,
                                              Access_Date,
                                              UPPER(AuditAutoFireOffices) & '.' & Upper(AuditAutoFireRequestee) As 'Audit Correlation Key',
                                              //Date(Max(Access_Date)) As Access_Date1,
                                              //Date(Date#(Completed_Date,'DDD MMM DD hh:mm:ss TTT YYYY'),'DD-MM-YYYY') as Completed_Date1,
                                              //date(date#(mid(Completed_Date,9,3)&' '&mid(Completed_Date,6,2)&' '&right(Completed_Date,4),'MMM DD YYYY')) As Access_Date,
                                              //Date(Date#(Subfield(Completed_Date,'',2)&''& Subfield(Completed_Date,'',3)&''& Subfield(Completed_Date,'',4)&''&Subfield(Completed_Date,'',6),'MMM D hh:mm:ss YYYY')) AS Access_Date,
                                              //Timestamp#(Mid(Completed_Date,6,16)&Right(Completed_Date,4),'MMM DD hh:mm:ss YYYY')as Access_Date1,
                                              //Replace(Replace(Replace(Completed_Date, ' CDT', ''), ' CST', ''), ',', '') As Access_Date,
                                              //date(timestamp#(Replace(Replace(Replace(Completed_Date, ' CDT', ''), ' CST', ''), ',', ''), 'WWW MMM DD hh:mm:ss YYYY'), 'MM-YYYY') As Access_Date,
                                              date(date(floor(CREATED/1000/60/60/24))+date('1970-01-01')) as LCMCalendarDate,
                                              TARGET As AuditTarget,
                                              TemplateName,
                                              TemplateName & ' - 1 - Illinois'  As BundleTypeKey,
                                              EmailSentTo,
                                              AuditEmailSentSubject,
                                              SOURCE As "Audit Requested By",
                                              TextBetween(ATTRIBUTES, '<entry key="Business Case" value="', '"/>') As "Audit Business Case",
                                              TextBetween(TextBetween(ATTRIBUTES, '<entry key="Approved by RA" value="', '"/>'), '(', ')') As "Audit Approved by RA",
                                              TextBetween(ATTRIBUTES, '<entry key="Completed by (PCU)" value="', '"/>') As "AuditAutoFire Approved by PCU",
                                              TextBetween(ATTRIBUTES, '<entry key="Completed by (DSA)" value="', '"/>') As "AuditAutoFire Approved by DSA",
                                              TextBetween(ATTRIBUTES, '<entry key="Completed by (Manager)" value="', '"/>') As "AuditAutoFire Approved by Manager",
                                              TextBetween(TextBetween(ATTRIBUTES, '<entry key="Approved by Manager" value="', '"/>'), '(', ')') As "Audit Approved by Manager"
                                              //TextBetween(TextBetween(ATTRIBUTES, '<entry key="Requested By" value="', '"/>'), '(', ')') As "Audit Requested By"
                                              FROM [..\..\QVD\SPT_AUDIT_EVENT.qvd] (qvd); 

                                               

                                              I hope I am making enough sense for you to figure what   I am looking for. I really appreciate your help.

                            • Re: Max function for date is not working
                              Diwas Karki

                                   Hello Stefan,

                              I had to change the date filed to be in a date format which was a pain in the butt. Then, I had to do a RESIDENT LOAD and a right join and then group by.

                              Thanks for your help.