Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ghdunn2000
New Contributor II

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

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:

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

10 Replies
Frank_Hartmann
Honored Contributor II

Re: Max/Group by not quite sufficient

Try like that:


Straight Table:

Dim  = Server, App

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

hope this helps

antoniotiman
Honored Contributor III

Re: Max/Group by not quite sufficient

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

ghdunn2000
New Contributor II

Re: Max/Group by not quite sufficient

Frank...many thanks...

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

Frank_Hartmann
Honored Contributor II

Re: Max/Group by not quite sufficient

Unbenannt.png

ghdunn2000
New Contributor II

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?

Frank_Hartmann
Honored Contributor II

Re: Max/Group by not quite sufficient

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

MVP
MVP

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:

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

ghdunn2000
New Contributor II

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!

ghdunn2000
New Contributor II

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!