Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This is an invalid expression apparently
https://community.qlik.com/t5/New-to-QlikView/Show-only-latest-date/td-p/1055183
I'm afraid this doesn't work for my situation
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
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?