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

[shiveshsingh]

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

[gp_oconnor]

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.

[Anonymous]

HI Sunny,

No multiple data at the same time. I have tried DISTINCT and QV is not taking it.

[johnca]

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

[Anonymous]

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.

[gp_oconnor]

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.

[qlikviewwizard]

Hi mbefkadu

Please close the thread by selecting Mark As Correct Answer/Helpful.

Thank you.