Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Oggy172
Creator
Creator

Returning data based on Max Value

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?

 

Labels (1)
1 Solution

Accepted Solutions
fldc2500
Partner - Contributor III
Partner - Contributor III

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
];

 

View solution in original post

2 Replies
fldc2500
Partner - Contributor III
Partner - Contributor III

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
];

 

Oggy172
Creator
Creator
Author

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!!