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

[diwaskarki]

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.

[swuehl]

Do you want to find the max access date per a certain grouping entity? Like max date per Audit ID or something like that?

You can use a key field that you use in a group by clause in your resident load and join back to your fact table like shown in

String Field on Maximum Date in Set Analysis

[diwaskarki]

A user(signon_id) can have multiple access_date for a particular office.(office_code).

I want to find the max date per SIGNON_ID FOR A PARTICULAR OFFICE_CODE.

[swuehl]

Then just use OFFICE_CODE as key field and GROUP BY clause field as shown in the post I've linked to.

[diwaskarki]

     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.