Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
];
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
];
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!!