topic Getting data for the latest event in a project in QlikView

Getting data for the latest event in a project

Tue, 14 Jan 2014 08:59:43 GMT

Hi all.

Qlikview's behavior is baffling me, and I'm wondering if I can get some help.

I have a workbook that contains data for a number of different projects. Each project is associated with a number of events, and each event has a date and a value associated with it. I'm interested in getting a table that displays for each project the value associated with the latest event associated with the project. So for example if we had the following data

Project	Event	Date	Value
A	eA1	2014-01-02	5
A	eA2	2014-01-06	6
A	eA3	2014-01-10	7
B	eB1	2014-01-01	8
B	eB2	2014-01-07	9
C	eC1	2014-01-03	10
C	eC2	2014-01-04	11

I'd like to create a table that looks like this:

Project	Latest date	Latest value
A	2014-01-10	7
B	2014-01-07	9
C	2014-01-04	11

Every project has at least one event. All the values are positive, and no two events occur on the same date for the same project.

For this table, Project is the dimension, and Latest date is just max([Event Date]). But I'm having trouble with Latest value. My attempts are somewhere in this realm:

only( if ([Event Date] = aggr(max([Event Date]), [Project]), [Event Value]) )

This actually produces the correct answer for some of the projects, but there are other projects that get no value in the table at all. Further investigation shows that for some -- but not all -- of the projects [Event Date] = aggr(max([Event Date]), [Project]) evaluates to false for every row. I have been unable to determine a commonality among the projects that act like this. I cannot understand this behavior; surely some date must equal the max of the date. So perhaps I am just misunderstanding the semantics of the aggr function.

Any ideas? Any pointers would be much appreciated.

Thanks!

Re: Getting data for the latest event in a project

Tue, 14 Jan 2014 09:06:52 GMT

And of course if there's a simpler or better way to accomplish this, I would love to hear it.

Thanks again!

Re: Getting data for the latest event in a project

Tue, 14 Jan 2014 09:18:23 GMT

I would rather work in the script for this one creating a flag via resident load and a group by?

Re: Re: Getting data for the latest event in a project

maxgro — Tue, 14 Jan 2014 09:37:54 GMT

try with

dimension = Project

expression = FirstSortedValue(Date, -Date)

expression = FirstSortedValue(Value, -Date)

Please see attachment

Regards

Re: Re: Getting data for the latest event in a project

Tue, 14 Jan 2014 17:35:38 GMT

This does work for my stripped down example, but my example (evidently) doesn't capture all the complexity in my workbook; adapting this answer to my workbook gives a "-" value indicating an error somewhere that I can't determine. In the end I solved this in my load script. Thanks for the suggestion, though -- I'll keep this (very helpful!) function in mind!

Re: Re: Getting data for the latest event in a project

Tue, 14 Jan 2014 17:37:54 GMT

Yes, that did it. Thank you! It seems that one of the subtleties of Qlikview development is when to munge data in the load scripts vs in Qlikview itself. Thanks again!