topic FirstSortedValue VS Max in QlikView

FirstSortedValue VS Max

Tue, 11 Jan 2011 17:43:15 GMT

Hi,

i don't understand because the 2 scripts are different....

scriptA:

load key,

max(x) as x

resident myTable

group by key;

left join

LOAD key,

val

resident myTable;

scriptB:

load key,

firstsortedvalue(val, -x) as val

resident myTable

group by key;

Thanks!

FirstSortedValue VS Max

Oleg_Troyansky — Tue, 11 Jan 2011 18:41:50 GMT

From the first look the thwo scripts should produce identical results - except maybe for a minor syntax issue. If I'm not mistaken, you need to explicitly multiply x * -1 instead of simply using "-x".

Other than that, I can't see a difference...

FirstSortedValue VS Max

johnw — Wed, 12 Jan 2011 00:33:42 GMT

Well, there's the trivial fact that the first table has x in it and the second does not. I assume that's not your concern.

They can produce different results depending on your data. Take this table for example:

myTable:
key, x, val
1, 1, a
1, 2, b
1, 2, c
2, 1, a
2, 2, b

I expect these tables to result:

scriptA:
key, x, val
1, 2, b
1, 2, c
2, 2, b

scriptB:
key, val
1, null
2, b

The difference results from having two different val corresponding to key = 1 and x = 2. Firstsortedvalue() will return null when there are multiple values that are first by your sort order, while your left join will generate an extra row for each such value.

I don't know if that's the specific difference you're seeing since you gave no example data or example results, but it's A difference between the two scripts.

FirstSortedValue VS Max

Fri, 14 Jan 2011 09:37:26 GMT

Thank you all for the answers!

My problem is how John says: Firstsortedvalue() return null when there are multiple values that are first by your sort order.

Thansk again