Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Sept. 16, Qlik Product Portfolio Strategy and Roadmap for Data Integration 11 AM ET REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Having difficulty with working aggr function

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!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try: firstsortedvalue(ReportStatus,-ModifiedDate)


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

Try: firstsortedvalue(ReportStatus,-ModifiedDate)


talk is cheap, supply exceeds demand

View solution in original post

Not applicable
Author

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?

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
stevelord
Specialist
Specialist

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.