topic Re: FirstSortedValue with multiple lowest sort-order in QlikView

FirstSortedValue with multiple lowest sort-order

danieloberbilli — Thu, 28 May 2015 00:38:12 GMT

Hi,

I would like to understand the FirstSortedValue() function in the script. If I have multiple lowest sort-orders...how can I still get a value in my output table object? When trying some expressions within the function like MinString(SortOrder) I get 'nested aggregation-errors' when reloading.

Please find attached an example.

Data:

LOAD * INLINE [

EmployeeID, Manager, SortOrder, RowID

1, B, 2, 1

1, C, 3, 2

1, A, 1, 3

1, A, 1, 4

2, D, 2, 5

2, A, 1, 6

3, F, 1, 7

];

Data2:

Load EmployeeID,

firstsortedvalue(Manager, SortOrder) as FirstManager Resident Data

group by EmployeeID;

Re: FirstSortedValue with multiple lowest sort-order

anbu1984 — Thu, 28 May 2015 05:17:46 GMT

Try adding distinct like below. Ignore the errors appearing and run the script

firstsortedvalue(distinct Manager, SortOrder)

Re: FirstSortedValue with multiple lowest sort-order

sujeetsingh — Thu, 28 May 2015 05:24:11 GMT

Anbu has provided a better way to remove the redundancy in your data.

Re: FirstSortedValue with multiple lowest sort-order

danieloberbilli — Thu, 28 May 2015 05:24:27 GMT

Wow, it works - that is weired...so is this actually a bug?

Re: FirstSortedValue with multiple lowest sort-order

anbu1984 — Thu, 28 May 2015 05:31:46 GMT

Yes

Re: FirstSortedValue with multiple lowest sort-order

FakeJupiter — Wed, 04 Dec 2019 09:02:15 GMT

Is there any way of doing this from in the chart expression view? I'm having exactly the same problem, but it still persists even if I use Distinct.