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: 
Anonymous
Not applicable

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

LOAD App,
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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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:

LOAD * Inline [

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)

load

Server, max(Date) as Date,

-1 as FlagServerChangedGroupBy

Resident Table

Group by Server;

Result:

NoConcatenate load

App,

Server,

Date,

Alt(FlagServerChangedGroupBy, 0) as FlagServerChangedGroupBy,

Peek('Server')<>Server as FlagServerChangedPeek

Resident Table

order by Server, Date desc;

DROP Table Table;

1.png

View solution in original post

10 Replies
Frank_Hartmann
Master II
Master II

Try like that:


Straight Table:

Dim  = Server, App

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

hope this helps

antoniotiman
Master III
Master III

Hi Gerald,

may be this

Table:
LOAD * Inline [
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
LOAD Max(Date) as Date
Resident
Table
Group By Server;

Regards,

Antonio

Anonymous
Not applicable
Author

Frank...many thanks...

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

Frank_Hartmann
Master II
Master II

Unbenannt.png

Anonymous
Not applicable
Author

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?

Frank_Hartmann
Master II
Master II

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

maxgro
MVP
MVP

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:

LOAD * Inline [

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)

load

Server, max(Date) as Date,

-1 as FlagServerChangedGroupBy

Resident Table

Group by Server;

Result:

NoConcatenate load

App,

Server,

Date,

Alt(FlagServerChangedGroupBy, 0) as FlagServerChangedGroupBy,

Peek('Server')<>Server as FlagServerChangedPeek

Resident Table

order by Server, Date desc;

DROP Table Table;

1.png

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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