topic Re: How to show latest data by area in QlikView

How to show latest data by area

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to show latest data by area

sunny_talwar — Tue, 12 Jun 2018 16:28:04 GMT

Try this

Dimension

Area

Expression

TimeStamp(Max([Date and Time]))

FirstSortedValue(Data, -[Date and Time])

Re: How to show latest data by area

Anonymous — Wed, 13 Jun 2018 12:36:20 GMT

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

sunny_talwar — Wed, 13 Jun 2018 15:56:04 GMT

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

Re: How to show latest data by area

Anonymous — Wed, 13 Jun 2018 16:05:39 GMT

It comes out blank, no value.

Re: How to show latest data by area

sunny_talwar — Wed, 13 Jun 2018 16:13:55 GMT

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‌

Re: How to show latest data by area

shiveshsingh — Wed, 13 Jun 2018 17:58:17 GMT

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

];

Re: How to show latest data by area

qlikviewwizard — Thu, 14 Jun 2018 04:36:38 GMT

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

];

Re: How to show latest data by area

vipin_mishra479 — Thu, 14 Jun 2018 06:50:22 GMT

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

Re: How to show latest data by area

qlikviewwizard — Thu, 14 Jun 2018 09:14:15 GMT

Hi mbefkadu

Did you get the answer?

Re: How to show latest data by area

Anonymous — Thu, 14 Jun 2018 09:18:24 GMT

Thanks Arjun! As you've suggested I have amended the load script to

Load Date#(Timestamp, 'MM/DD/YYYY hh:mm:ss') as 'Date and Time',

This seem to do the trick. I was bit confused as in the row data 'date and time' is labeled as 'Timestamp'. I have then used what Sunny has suggested and also what you have suggested but I am getting data for the latest 'Date and Time' not the latest data for each area. Can you suggest anything else?

Re: How to show latest data by area

Anonymous — Thu, 14 Jun 2018 09:21:05 GMT

Thanks Vipin! this has worked as well without changing the load script but I am still getting Data with latest date. What I am looking to show is the latest reported data for each area. Any suggestion?

Re: How to show latest data by area

Anonymous — Thu, 14 Jun 2018 09:22:25 GMT

Hi Arjun,

Just replied back above. I am still struggling in showing the latest reported data only for each area.

Re: How to show latest data by area

sunny_talwar — Thu, 14 Jun 2018 15:34:29 GMT

Use this as an expression

TimeStamp(Max([Date and Time]))

Re: How to show latest data by area

Anonymous — Thu, 14 Jun 2018 15:51:00 GMT

Thanks Sunny! I am still getting the same result as above. It seems like it is picking up empty "Data" values from the table for the latest "Date and Time". Is there away I can put a condition to show latest data by area only for data with values.

Thanks

Re: How to show latest data by area

sunny_talwar — Thu, 14 Jun 2018 15:52:09 GMT

What exactly is wrong? I mean what exactly are you looking to get?

Re: How to show latest data by area

Anonymous — Thu, 14 Jun 2018 16:00:04 GMT

I want to show latest Data by area. So as you can see above on the screen shot I have the latest data for some areas but not for all. It is picking up data for the latest submission Date and Time. You see each submission by client can be different containing data for certain locations only, as some areas might not report daily. So what I want to show is data for each area with their last submission Date and Time.

Thx

Re: How to show latest data by area

sunny_talwar — Thu, 14 Jun 2018 16:02:27 GMT

Make sense... can you tell me what all dimensions and expressions are using?

Re: How to show latest data by area

Anonymous — Thu, 14 Jun 2018 16:21:08 GMT

I am using Area as dimension and the two expressions as attached above, Data and Date and Time range. I also have other dimensions as "Provider" and "Authority" that I am not using in this table.

Re: How to show latest data by area

sunny_talwar — Thu, 14 Jun 2018 16:35:37 GMT

Can you have multiple data at the same time? Try this

FirstSortedValue(DISTINCT Data, -[Date and Time])