Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
diwaskarki
Creator II
Creator II

Max function for date is not working

Hello , my data in a multibox looks like this:

inactive.png

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

14 Replies
diwaskarki
Creator II
Creator II
Author

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
MVP
MVP

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
Creator II
Creator II
Author

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
MVP
MVP

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

diwaskarki
Creator II
Creator II
Author

     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.