10 Replies Latest reply: Dec 1, 2017 8:48 AM by Gerald Dunn

# Max/Group by not quite sufficient

Qlikview 11.2

I have some data of applications with Applications and Retirement Dates being shared across Servers in a big company:

App   RetirementDate Server

A       2017                     1

B       2018                      1

C       2019                      1

B       2018                      2

D       2020                       2

I want to calculate the Max date for each server, so I know which app will be resident on the server for the longest period.  This I can do with Max(RetirementDate) Group by Server.

However, I also want to bring the Application(s) relating to that date into the results...so I no only know which dates but the Application(s) associated with those dates.

Just can't get anything to work

I have also tried Peek

Server

Retirement Date

if(Peek(Retirement Date)=Retirement Date,1,0) as Flag
From Source
order by App,Retirement Date;

And again, have tried all combinations of Order By and it just does not seem to bring the right results

Any other ideas?

Thanks

Gerald

• ###### Re: Max/Group by not quite sufficient

Try like that:

Straight Table:

Dim  = Server, App

Expression  = aggr(max(RetirementDate,Server),Server)

hope this helps

• ###### Re: Max/Group by not quite sufficient

Frank...many thanks...

Now getting "aggr is not a valid function"  ??

• ###### Re: Max/Group by not quite sufficient

Frank...hi...sorry, just got back to this.

I wanted to do it on Load, not in a chart.

Would that approach work on Load?

• ###### Re: Max/Group by not quite sufficient

if you need to  do this on script level then see Antonios solution!

• ###### Re: Max/Group by not quite sufficient

Frank...sincere apologies for delay...got tied up with something else at work and you know how it is!

• ###### Re: Max/Group by not quite sufficient

Hi Gerald,

may be this

Table:
App Date Server
A 2017 1
B 2018 1
C 2019 1
B 2018 2
D 2020 2
E 2020 2
]
(delimiter is spaces);
Inner Join
Resident
Table
Group By Server;

Regards,

Antonio

• ###### Re: Max/Group by not quite sufficient

Antonio..sincere apologies for delay...got tied up with something else at work and you know how it is!

• ###### Re: Max/Group by not quite sufficient

another one with peek;

if you have the same max date by server in different rows,

with "group by" you get all the rows with max date by server

with peek only the first row with max date by server

Table:

App Date Server

A 2017 1

B 2018 1

C 2019 1

B 2018 2

D 2020 2

E 2020 2

](delimiter is spaces);

left join (Table)

Server, max(Date) as Date,

-1 as FlagServerChangedGroupBy

Resident Table

Group by Server;

Result:

App,

Server,

Date,

Alt(FlagServerChangedGroupBy, 0) as FlagServerChangedGroupBy,

Peek('Server')<>Server as FlagServerChangedPeek

Resident Table

order by Server, Date desc;

DROP Table Table;

• ###### Re: Max/Group by not quite sufficient

Massimo..sincere apologies for delay...got tied up with something else at work and you know how it is!