Max Date

[khaycock]

I have a set of data where I am going to load in a new report daily. Some of the lines will be duplicated but will have different 'Last Observed' date and times. I want to only show the line of data that has the most recent 'Last Observed' date and time. 

I have tried playing around with Max (see MaxObserved) but and it spit out the correct date but didn't restrict the data like I was hoping it would. Is it possible to use Max when a time is involved too?

My overall plan is to load each version of report into a concatenated QVD file, so I'm aiming to get the data joined correctly without any duplicated data.

[Claudiu_Anghelescu]

try Max(Date(LastObserved))

[khaycock]

This is an invalid expression apparently

[Claudiu_Anghelescu]

https://community.qlik.com/t5/New-to-QlikView/Show-only-latest-date/td-p/1055183

[khaycock]

I'm afraid this doesn't work for my situation

[waszcma1]

Try load data and group by primary key and use max([Last Observed No UTC])

example:

TableName:

Load

PrimaryKey,

max([Last Observed No UTC]) as [Last Observed No UTC]

From source.qvd(qvd) // or any source you are using

Group by PrimaryKey;

you should have only one record per each PriamryKey

[khaycock]

Because I am concatenating two tables of data for now, and either have a natural primary key, I had to resident the load of the key underneath, but still had no luck. Here is my script:

Vulnerabilities:
LOAD Plugin,
[Plugin Name],
Family,
Severity,
[IP Address],
[First Discovered],
// Max(Date([Last Observed])) AS LastObserved,
(Replace([Last Observed],'UTC','')) as [Last Observed1],
Plugin & [IP Address] AS KEY
FROM
[Data.csv];

Concatenate
(Vulnerabilities)
LOAD Plugin,
[Plugin Name],
Family,
Severity,
[IP Address],
[First Discovered],
// Max(Date([Last Observed])) AS LastObserved,
(Replace([Last Observed],'UTC','')) as [Last Observed1],
Plugin & [IP Address] AS KEY
FROM
[Data.csv];

NoConcatenate
Temp:
Load
KEY,
max([Last Observed1]) AS LASTOBSERVED
RESIDENT Vulnerabilities
GROUP BY KEY;

Is there another way to do this?