12 Replies Latest reply: Dec 11, 2017 4:37 PM by Diwas Karki RSS

    how to bring in max date

    Diwas Karki

      I have a field in my table called "Completed_Date" that displays data like this : Wed, Jun 29 13:38:37 CDT 2016.

      There are other fields in the table like operator, office etc.

      An operator can have multiple rows for the same office but with different "Completed_Date". How can I bring in the latest date for an operator if other field value is the same?

      It can either be in the script or some settings in the straight table itself. Here is what my table looks like for a better understanding.

       

      new pic.pngId really appreciate the help.

        • Re: how to bring in max date
          Raghav B

          first change the data format.

          do resident load as below

           

          load distinct operator,office,max(date)

          resd..

          group by operator,office

          • Re: how to bring in max date
            Anil Babu

            First play with date format and then Load as below

             

            Main:

            Load Date(Date#(Completed_Date,'DDD, MMM DD hh:mm:ss TTT YYYY'),'DD-MM-YYYY') as Completed_Date From Table;

            Right Join (Main)

            Load Max(Completed_Date) as Completed_Date

            Resident Main;

              • Re: how to bring in max date
                Diwas Karki

                Anil ,

                That script didn't bring in anything although it ran without any errors. (Very strange).

                Im thinking may be I can use the max function if  I could do a substring or something and extract this from my completed_date field: Apr 7 2017

                Even better if its in this format 04/02017.

                Thanks for your help.

                  • Re: how to bring in max date
                    Anil Babu

                    Could be format issue? Will you provide Excel example file to test in my machine? Even better, Inline data with QVW

                      • Re: how to bring in max date
                        Diwas Karki

                        I created a small test spreadsheet for you :

                        test.png

                          • Re: how to bring in max date
                            Anil Babu

                            Image won't help rather please attach in excel

                              • Re: how to bring in max date
                                Diwas Karki

                                Hello Anil,

                                I don't see an option to attach files.. sorry about that

                                  • Re: how to bring in max date
                                    Anil Babu

                                    If you start reply - there is option called "use advance editor" in the right corner of top in your body message - just click that - After that there is option called attach in the bottom of same body message. From there you can upload the file currently you worked for. And just use ctrl+s or add reply on the left hand side message..

                                      • Re: how to bring in max date
                                        Diwas Karki

                                        Anil I don't see that option. However, I have been able to extract the date in a proper date format. It looks like this now : 10/19/2017.

                                        And I tried to run the max script you mentioned above. I think it brought the maximum of all the dates in the table in this format "42971". I think its not correlating to the table "Inactive_AutoFire_Users_Report" . I need to have it correlated.

                                        I am pasting my whole script here for you to see what  I have.

                                         

                                        SPT_AUDIT_EVENT:
                                        LOAD ID As AuditID,
                                        MODIFIED As AuditModified,
                                        ACTION As AuditAction,
                                        AuditAutoFireRequestee,
                                        AuditAutoFireOffices,
                                        Completed_Date,
                                        Access_Date,
                                        UPPER(AuditAutoFireOffices) & '.' & Upper(AuditAutoFireRequestee) As 'Audit Correlation Key',
                                        date(date(floor(CREATED/1000/60/60/24))+date('1970-01-01')) as LCMCalendarDate,
                                        TARGET As AuditTarget,
                                        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);

                                        Right Join(SPT_AUDIT_EVENT)
                                        Load Max(Access_Date) As Access_Date1
                                        RESIDENT SPT_AUDIT_EVENT;

                                        Inactive_AutoFire_Users_Report:
                                        LOAD
                                        Date(REPORT_DATE,'DD-MMM-YY') As [Report Date],
                                        SIGNON_ID,
                                        OPERATOR_INITIAL,
                                        OPERATOR_NAME,
                                        OFFICE_CODE,
                                        JOB_STATION
                                        Date(LAST_LOGGED_IN_DATE,'DD-MMM-YY') As [Last Logged In Date],
                                        INACTIVE_DURATION_IN_DAYS,
                                        //date(date(floor(LAST_LOGGED_IN_DATE/1000/60/60/24))+date('1970-01-01')) as LCMCalendarDate1,
                                        (Upper(OFFICE_CODE) & '.' & Upper(SIGNON_ID)) As 'Audit Correlation Key',
                                        INACTIVE_DURATION_IN_MONTHS
                                        FROM
                                        [..\..\QVD\Inactive_AutoFireUsers.qvd](
                                        qvd);

                                        Hopefully this is making sense. Thanks for your help.

                          • Re: how to bring in max date
                            Tamil Nagaraj

                            Hi Diwas,

                             

                            Data:

                            Load Person,

                                New_Office,

                                Completed_Date,

                                Date#(Subfield(SubField(Completed_Date,', ',-1),' CDT',1),'MMM DD hh:mm:ss') as FormattedDate

                            From

                            Source;

                             

                            Right Join

                            Load Person,

                            New_Office,

                            Max(FormattedDate) as FormattedDate

                            Resident Data Group by Person, New_Office;

                             

                            DROP Field FormattedDate;

                            • Re: how to bring in max date
                              john lewis

                              Try max(Timestamp(CompletedDate)) as you are having timestamp in your date field