topic Re: FirstSortedValue Problem in QlikView

FirstSortedValue Problem

Fri, 26 Dec 2014 22:04:52 GMT

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:

Key	Rule	Day	Qty	Current Total
ABC	1	1	20	20
ABC	1	2	25	25
ABC	1	3	20	20
ABC	1	4	35	35
ABC	1	5	40	40
ABC	1	6	-1	null (should be 40)
ABC	1	7	-1	null (should be 40)
ABC	1	8	-1	null (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

Re: FirstSortedValue Problem

nagaiank — Fri, 26 Dec 2014 23:43:45 GMT

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

];

Re: FirstSortedValue Problem

Sat, 27 Dec 2014 03:57:13 GMT

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,

Re: FirstSortedValue Problem

Mon, 29 Dec 2014 12:44:57 GMT

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

Re: FirstSortedValue Problem

anbu1984 — Mon, 29 Dec 2014 13:05:01 GMT

Check this