topic Re: Finding activity from latest date in QlikView

Finding activity from latest date

Thu, 13 Jun 2013 15:38:32 GMT

Hello,

I have a large table of company, action date, activity code and result code. There can be multiple activities for a company, on different days, and also on the same day. My action date is MM/DD/YY hh:mm, so it's usually unique. I want to find the result code from the last action taken for each company.

I thought this would be easy: FIRSTSORTEDVALUE(ResultCode,-ActionDate), but that returns uniformly NULLs on my QV table.

Ah-ha, I thought, this is a case for AGGR! So I tried AGGR(FIRSTSORTEDVALUE(ResultCode,-ActionDate), Company) but that also returned NULLs.

Any thoughts on how I could accomplish this?

Re: Finding activity from latest date

Thu, 13 Jun 2013 15:49:02 GMT

It's a little bit difficult to propose something this way but have you tried the function MaxString() and Set Analysis? That should get it done.

Re: Finding activity from latest date

Gysbert_Wassenaar — Thu, 13 Jun 2013 15:58:11 GMT

Make sure you're date field isn't a string, but a real date field, i.e. it has at least a numeric value. Use the date# function if you need to make a date from a string. It's recommended to do this in the script.

If that isn't the problem please prepare and post a sample document.

Re: Finding activity from latest date

Anonymous — Thu, 13 Jun 2013 16:08:12 GMT

As for the AGGR - you don't need it if Company is one of the dimensions in your table.

Re: Finding activity from latest date

Thu, 13 Jun 2013 16:48:24 GMT

It is a real date - in fact, in the script I create a field called "FullTime" with:

Date#

(Date(OnDate+OnTime, 'MM/DD/YY hh:mm') & ':' &Secs(Records),'MM/DD/YY hh:mm:ss') AS FullTime,

where SECS is a function I created in VB. This gives me a unique value to use with FirstSortedValue, but it's still not working in this case (note: I use FSV(field,-FullTime) in a host of other tables and QV files, so I know it works most of the time; just can't figure out why it's not working here.