topic Re: Having difficulty with working aggr function in QlikView

Having difficulty with working aggr function

Tue, 16 Jul 2013 16:25:54 GMT

Hi everyone,

I have this table:

LOAD * Inline

[ UserId, ModifiedDate, ReportStatus

204190,6/12/2012,Completed

204190,1/22/2013,Waiting for Screening ];

I am trying to return the latest ReportStatus based on UserId.

This is my aggr function, which has no problem returning the date, but once I try to add in ReportStatus, the function returns nothing:

Works:

=Aggr(max(ModifiedDate), UserId)

Doesn't work, but need it to!

=if(Aggr(max(ModifiedDate), UserId), ReportStatus)

I'm pounding my head against the desk trying to figure out why this doesn't work!

Re: Having difficulty with working aggr function

Gysbert_Wassenaar — Tue, 16 Jul 2013 16:38:15 GMT

Try: firstsortedvalue(ReportStatus,-ModifiedDate)

Re: Having difficulty with working aggr function

Tue, 16 Jul 2013 16:53:26 GMT

That worked, thank you! Maybe I was over-thinking it.

Is there a way to only display the expression if the user selects a UserId?

Re: Having difficulty with working aggr function

Gysbert_Wassenaar — Tue, 16 Jul 2013 16:59:46 GMT

if(getselectedcount(UserId)=1, firstsortedvalue(ReportStatus,-ModifiedDate))

Re: Having difficulty with working aggr function

stevelord — Tue, 16 Jul 2013 20:04:41 GMT

Hey I have one more scenario where this didn't pick the right thing.

In this case, the timestamp/date is identical down to the second but one has 0 value and the other has some number value. I've tried a variety of approaches to make it pick the one with the value- including an attempt to exclude the 0 value in the script that QV somehow ignored.

Anyone have an idea for firstsorted(fieldname, -date) that would pick the greatest value of that fieldname if the date was identical down to the second?

Like:

firstsortedvalue(firstsortedvalue(fieldname, -date), -fieldname)

(That formula didn't actually work, but it's correct logically I think.)

-----------Update----------------------------

Update this did the trick: firstsortedvalue(fieldname, -(date+fieldname))

So now it is date + fieldname value, and if fieldname value is 0 it loses.

Also I had one that was a fieldname concatenated with text concatenate with another fieldname (blood pressure) and I just had it go with

firstsortedvalue(fieldname, -(date+fieldname/fieldname2)) //one of the fields had a 0 and another had a bogus high number, so I used divide to make it remove itself and yield to some real value on the same timestamp.

----update update----

and date+fieldname won't work if the timestamp matches to the second and the fieldnames have identical values. Solving this was a matter of adding a bunch of other related fieldnames that might have 0 on the less complete record from one source and values on the complete record from a parallel source to make QlikView just pick one rather than give another null.

Anyway, I think that solves for almost every loose end now. If all the number values across the span of fields were identical, we might figure a way to have it look at the greater alphabetical value of the two sources.... nest if statements or something...

Gysbert's solution was great to throw around.