Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have data uploaded by clients daily showing RAG status. Data entered is not always complete therefore I would like to build a straight table showing the latest submitted RAG status by area. Can you please help how I can do this at the front end.
Here is the data I have at the moment
Area | Date and Time | Data |
B | 5/2/2018 10:47:27 | Amber |
C | 5/2/2018 10:47:27 | Red |
D | 5/2/2018 10:47:27 | Amber |
A | 5/3/2018 10:40:00 | Black |
C | 5/3/2018 10:40:00 | Black |
E | 5/3/2018 10:47:00 | Green |
A | 5/4/2018 12:27:05 | Amber |
E | 5/4/2018 12:27:05 | Green |
B | 5/4/2018 12:27:05 | Black |
And this is what I would like to Display
Area | Date and Time | Data |
A | 5/4/2018 12:27:05 | Amber |
B | 5/4/2018 12:27:05 | Black |
C | 5/3/2018 10:40:00 | Black |
D | 5/2/2018 10:47:27 | Amber |
E | 5/4/2018 12:27:05 | Green |
Is this working?
Try this in your script
T:LOAD Area,Data,timestamp#([Date and Time],'DD/MM/YYYY hh:mm:ss') as [Date and Time] INLINE [
Area, Date and Time, Data
B, 5/2/2018 10:47:27, Amber
C, 5/2/2018 10:47:27, Red
D, 5/2/2018 10:47:27, Amber
A, 5/3/2018 10:40:00, Black
C, 5/3/2018 10:40:00, Black
E, 5/3/2018 10:47:00, Green
A, 5/4/2018 12:27:05, Amber
E, 5/4/2018 12:27:05, Green
B, 5/4/2018 12:27:05, Black
];
Dimension : Area
Expression :
=TimeStamp(Max([Date and Time]))
=FirstSortedValue(Data, -[Date and Time])
Hi Michael
Attached seems to give the answers you are after. Bit of a hack using Aggr, probably need to refine the timestamp handling and not sure how it will perform under high volumes.
HI Sunny,
No multiple data at the same time. I have tried DISTINCT and QV is not taking it.
Maybe this? It results in your desired results table.
data:
Load
Area,
LastValue(TimeStamp) as LastTimeStamp,
LastValue(Data) as LastData
Group By Area;
Load
Area,
Date(Date#([Date and Time],'M/D/YYYY hh:mm:ss'),'M/D/YYYY hh:mm:ss') as TimeStamp,
Data;
Load * Inline [
Area, Date and Time, Data
B, 5/2/2018 10:47:27, Amber
C, 5/2/2018 10:47:27, Red
D, 5/2/2018 10:47:27, Amber
A, 5/3/2018 10:40:00, Black
C, 5/3/2018 10:40:00, Black
E, 5/3/2018 10:47:00, Green
A, 5/4/2018 12:27:05, Amber
E, 5/4/2018 12:27:05, Green
B, 5/4/2018 12:27:05, Black
];
HTH,
John
Thanks Gavin. It is still getting data for the latest 'Date and Time' not the latest data for each area. I think I can live with this asking clients to use filters to work it out themselves.
Hi Michael
I don't understand your response as my solution provided exactly the output required as per your original post? The straight table showed the most recent time as well as the latest status corresponding to that.
I've made a minor change to my original solution, please see attached.