Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

FirstSortedValue Problem

I have a data set similar to the below table.  There are 3 dimensions in my table: Key, Rule, and Day.  Qty is the field/expression that represents the quantity for each given Key, Rule, and Day.  The Current Total field should represent another expression... if Qty <> -1 then display Qty, else display the last value where Qty <> -1 (and by 'last' I specifically mean the maximum day).

When I use the following expression in a simple text box I get the correct result (40) that should display on all rows when Qty = -1: =FirstSortedValue({$<Qty = {"*"}-{"-1"}>}Qty,-Day)

However, when I use that exact same formula in my expression in the table for the Current Total field, it returns null values instead of 40:

                                                                                         

KeyRuleDayQtyCurrent Total
ABC112020
ABC122525
ABC132020
ABC143535
ABC154040
ABC16-1null (should be 40)
ABC17-1null (should be 40)
ABC18-1null (should be 40)

I am going to guess this has to do with aggregating data in a specific manner.  Any help would be greatly appreciated.

Thanks

1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: FirstSortedValue Problem

Check this

4 Replies
nagaiank
Valued Contributor III

Re: FirstSortedValue Problem

The following script will give a clue to get the result.

load *,If(Qty= -1,Peek(Total),Qty) as Total;

load * Inline [

Key,Rule,Day,Qty

ABC,1,1,20

ABC,1,2,25

ABC,1,3,20

ABC,1,4,35

ABC,1,5,40

ABC,1,6,-1

ABC,1,7,-1

ABC,1,8,-1

];

Capture.PNG

Not applicable

Re: FirstSortedValue Problem

Brendan,

  Qlikview can actually solve your problem.  Make a Straight Table Chart, put the Key, Rule, Day as Dimensions and make an expression on Qty and another with the following  IF (Qty > 0,Qty, MAX( ALL AGGR (Qty,Day))) , name it Current Total and voila.


  I usually don't do things at the script level unless necessary for performance or any other reason, because QlikView is a very powerful engine with tools and functions that can solve many of our daily work.


Regards,



Not applicable

Re: FirstSortedValue Problem

Mario,

Thanks for the suggestion.  What if, however, the latest Qty is not always the largest/max value?  How would you amend your expression so that it always picks the Qty for the max/largest date?

Thanks

anbu1984
Honored Contributor III

Re: FirstSortedValue Problem

Check this

Community Browser