topic Re: First sorted value equality condition- distinct does not work in App Development

First sorted value equality condition- distinct does not work

ayseayse — Wed, 10 Nov 2021 13:14:46 GMT

I used first sorted value to make my calculation, My aim was to take max of total, concatenate reasons while mat is same and print tur which corresponds to max total.

However there may be equality of total for different inputs(rows). In such cases, I would like to create a priorization among turs. If there is equality in among totals "bi" should be first to print then, "ek"and then "ka".

So below case instead of - , "bi" should be printed.

p.s.In case are two distinct line where tur and the total are the same, it should print tur itself in instead of "-"

Distinct does not work

Raw:
load
tur,
mat,
total,
reason

From mysource;

Calculation:
LOAD
FirstSortedValue(tur,-total,1) as "tur1",
mat as mat1,
max(total) as total1,
concat(reason) as reason1

Resident Raw
Group by mat

Re: First sorted value equality condition- distinct does not work

Or — Wed, 10 Nov 2021 13:54:38 GMT

FirstSortedValue() returns one value - the one matching the first sorted value (DISTINCT should break ties if they exist among the sorted values, but will not help if the maximum value corresponds to multiple dimension values, in which case it will return null). It will not return multiple dimension values as you seem to be trying to do. Perhaps you could consider breaking the ties by adding or subtracting values as appropriate, e.g.

if(tur='bi',total+0.0000000002, if(tur='ek',total+0.0000000001,total))

That should prevent ties from happening and pick out the tur you want based on the priority you listed.

Alternatively, you could probably add another load, grouped by mat and sorted by -total and tur1, and pull just the top row from that load for each mat, and then use that to get the value you want.