Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
diwaskarki
Creator II
Creator II

how to bring in max date

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.

15 Replies
tamilarasu
Champion
Champion

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

Anonymous
Not applicable

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

diwaskarki
Creator II
Creator II
Author

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.

Anil_Babu_Samineni

May be this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
diwaskarki
Creator II
Creator II
Author

Hello Anil,

Yes you are very close.

I had to do that but do a group by a key.

Anil_Babu_Samineni

Not sure, Why Aggregate needed?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)