Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Try like that:
Straight Table:
Dim = Server, App
Expression = aggr(max(RetirementDate,Server),Server)
hope this helps
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
Frank...many thanks...
Now getting "aggr is not a valid function" ??
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?
if you need to do this on script level then see antoniotiman solution!
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;
Frank...sincere apologies for delay...got tied up with something else at work and you know how it is!
Antonio..sincere apologies for delay...got tied up with something else at work and you know how it is!