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,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

          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?

                 

                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.