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 |
Try this
Dimension
Area
Expression
TimeStamp(Max([Date and Time]))
FirstSortedValue(Data, -[Date and Time])
Try this
Dimension
Area
Expression
TimeStamp(Max([Date and Time]))
FirstSortedValue(Data, -[Date and Time])
Thanks Sunny!
I've had no luck with that. The data is loaded to a google document by the clients and I am reloading data to QV every few hours from the google drive. Data reload will be different to the "Date and Time". Is that somehow connected?
I don't think it is... What exactly happened when you used the above two expressions?
It comes out blank, no value.
It seems like your date and time field may not be read as date by QlikView... read more on this here
Try this in your script and then do as suggested by Sunny
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
];
Hi,
Try like this.
ColorCode:
LOAD Area,DATE#([Date and Time],'M/D/YYYY hh:mm:ss') as [Date and Time],Data 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
];
You can try this
Dimension
Area
Expression
Exp1 : Timestamp(Max(Date#([Date and Time],'M/D/YYYY hh:mm:ss')))
Exp 2 : FirstSortedValue(Data, -Date#([Date and Time],'M/D/YYYY hh:mm:ss'))
Hi mbefkadu
Did you get the answer?