topic Re: Max function for date is not working in QlikView

Max function for date is not working

diwaskarki — Tue, 28 Nov 2017 21:08:57 GMT

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

Re: Max function for date is not working

swuehl — Tue, 28 Nov 2017 22:04:47 GMT

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

Re: Max function for date is not working

diwaskarki — Tue, 28 Nov 2017 23:00:55 GMT

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

Re: Max function for date is not working

swuehl — Tue, 28 Nov 2017 23:06:03 GMT

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.

Re: Max function for date is not working

diwaskarki — Wed, 29 Nov 2017 14:25:13 GMT

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.

Re: Max function for date is not working

swuehl — Wed, 29 Nov 2017 14:33:53 GMT

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;

Re: Max function for date is not working

diwaskarki — Wed, 29 Nov 2017 15:12:26 GMT

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

Re: Max function for date is not working

swuehl — Wed, 29 Nov 2017 16:18:17 GMT

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.

Re: Max function for date is not working

diwaskarki — Tue, 05 Dec 2017 21:40:37 GMT

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:

Re: Max function for date is not working

swuehl — Tue, 05 Dec 2017 22:35:15 GMT

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

Re: Max function for date is not working

diwaskarki — Mon, 11 Dec 2017 21:27:39 GMT

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.

Re: Max function for date is not working

swuehl — Mon, 11 Dec 2017 22:08:05 GMT

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

Re: Max function for date is not working

diwaskarki — Mon, 11 Dec 2017 22:54:20 GMT

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.

Re: Max function for date is not working

swuehl — Tue, 12 Dec 2017 07:37:09 GMT

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

Re: Max function for date is not working

diwaskarki — Wed, 13 Dec 2017 21:29:34 GMT

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.