Max function for date is not working

[diwaskarki]

Hello , my data in a multibox looks like this:

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

[swuehl]

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

[diwaskarki]

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

[swuehl]

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.

[diwaskarki]

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.

[swuehl]

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;

[diwaskarki]

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

[swuehl]

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.

[diwaskarki]

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:

[swuehl]

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