topic Max Date in QlikView

Max Date

khaycock — Sat, 16 Nov 2024 04:14:14 GMT

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.

Re: Max Date

Claudiu_Anghelescu — Fri, 08 Mar 2019 10:50:53 GMT

try Max(Date(LastObserved))

Re: Max Date

khaycock — Fri, 08 Mar 2019 11:57:29 GMT

This is an invalid expression apparently

Re: Max Date

Claudiu_Anghelescu — Fri, 08 Mar 2019 12:12:23 GMT

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

Re: Max Date

khaycock — Fri, 08 Mar 2019 14:23:10 GMT

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

Re: Max Date

waszcma1 — Fri, 08 Mar 2019 22:10:28 GMT

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

Re: Max Date

khaycock — Mon, 11 Mar 2019 10:07:38 GMT

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?