Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Id really appreciate the help.
Or you can try FirstSortedValue function in straight table.
Straight table:
Dimensions:
Persons
New_Office
Expression:
FirstSortedValue(Completed_Date,-Date#(Subfield(SubField(Completed_Date,', ',-1),' CDT',1),'MMM DD hh:mm:ss'))
Try max(Timestamp(CompletedDate)) as you are having timestamp in your date field
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.
May be this?
Right Join(SPT_AUDIT_EVENT)
Load Date(Max(Access_Date)) As Access_Date1
RESIDENT SPT_AUDIT_EVENT;
Hello Anil,
Yes you are very close.
I had to do that but do a group by a key.
Not sure, Why Aggregate needed?