4 Replies Latest reply: Jul 16, 2013 5:00 PM by Steve Lord RSS

    Having difficulty with working aggr function

      Hi everyone,


      I have this table:


      LOAD * Inline

      [          UserId, ModifiedDate, ReportStatus


                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:



      =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

          Try: firstsortedvalue(ReportStatus,-ModifiedDate)

            • Re: Having difficulty with working aggr function

              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
                Steve Lord

                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?




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


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


                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.