topic Returning data based on Max Value in Connectivity & Data Prep

Returning data based on Max Value

Oggy172 — Fri, 07 Feb 2025 15:36:26 GMT

Hi all,

I'm porting some QV documents to QS, and we have an automated QV task that dumps a chart to a .qvd

As I understand it, this functionality doesn't exist in QS, and so I need to do it within the load script and store the table as a qvd (I presume)

The try and describe the issue, we produce our products across various sites, and I need to ascertain when & where it was last produced.

e.g for the below table:

Product	Location	ProdDate
1234	London	20241112
5555	Paris	20241112
1234	Paris	20250101

My output should appear to be like:

Product	LastProduced	LastProduceDate
5555	Paris	20241112
1234	Paris	20250101

In QV, our table just has

Product

LastProduced = FirstSortedValue(distinct Company,-[Date])

LastProducedDate = date(Max (Date),'YYYYMMDD')

However, when done in a load script, I get a row for "London" and one for "Paris"

Any ideas on how I can achieve this?

Re: Returning data based on Max Value

fldc2500 — Fri, 07 Feb 2025 16:00:32 GMT

Hi,

For me the following seems to give the expected output in this example. I only formatted the date a little differently. I used a group by and a preceding load. Would this help you in any way?

T1:
LOAD
 Product,
 FirstSortedValue(distinct Location, -ProdDate) AS LastProduced,
 date(Max(ProdDate)) AS LastProducedDate 
group by Product
;
LOAD * INLINE [
Product, Location, ProdDate
1234, London, 12-11-2024
5555, Paris, 12-11-2024
1234, Paris, 01-01-2025
];

Re: Returning data based on Max Value

Oggy172 — Fri, 07 Feb 2025 16:08:59 GMT

Amazing, thank you.

I actually had pretty much what you had, but left Location in my group by from other testing, and so it created the additional row.

Many thanks!!