How to show latest data by area

[Anonymous]

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

[sunny_talwar · Accepted solution]

Try this

Dimension

Area

Expression

TimeStamp(Max([Date and Time]))

FirstSortedValue(Data, -[Date and Time])

[Anonymous]

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?

[sunny_talwar]

I don't think it is...  What exactly happened when you used the above two expressions?

[Anonymous]

It comes out blank, no value.

[sunny_talwar]

It seems like your date and time field may not be read as date by QlikView... read more on this here

Why don’t my dates work?

QlikView Date fields

Get the Dates Right‌

[shiveshsingh]

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

];

[qlikviewwizard]

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

];

[vipin_mishra479]

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'))

[qlikviewwizard]

Hi mbefkadu

Did you get the answer?