Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mbefkadu
New Contributor

How to show latest data by area

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

    

AreaDate and TimeData
B5/2/2018 10:47:27Amber
C5/2/2018 10:47:27Red
D5/2/2018 10:47:27Amber
A5/3/2018 10:40:00Black
C5/3/2018 10:40:00Black

E

5/3/2018 10:47:00

Green

A5/4/2018 12:27:05Amber
E5/4/2018 12:27:05Green
B5/4/2018 12:27:05Black

And this is what I would like to Display

AreaDate and TimeData
A5/4/2018 12:27:05Amber
B5/4/2018 12:27:05Black
C5/3/2018 10:40:00Black
D5/2/2018 10:47:27Amber
E5/4/2018 12:27:05Green
1 Solution

Accepted Solutions

Re: How to show latest data by area

Try this

Dimension

Area

Expression

TimeStamp(Max([Date and Time]))

FirstSortedValue(Data, -[Date and Time])

26 Replies

Re: How to show latest data by area

Try this

Dimension

Area

Expression

TimeStamp(Max([Date and Time]))

FirstSortedValue(Data, -[Date and Time])

mbefkadu
New Contributor

Re: How to show latest data by area

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?

Re: How to show latest data by area

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

mbefkadu
New Contributor

Re: How to show latest data by area

It comes out blank, no value.

Screen Shot 2018-06-13 at 5.04.38 PM.png

Re: How to show latest data by area

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
Honored Contributor

Re: How to show latest data by area

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

];

Arjunarao
Honored Contributor II

Re: How to show latest data by area

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

];

1.PNG

Capture.PNG

vipin_mishra479
Contributor II

Re: How to show latest data by area

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

Arjunarao
Honored Contributor II

Re: How to show latest data by area

Hi mbefkadu

Did you get the answer?

Community Browser