FirtSorted Value does not return expected value in some cases

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have a sample dataset like below and like to get distinct of Col1 with Max Count over Col2

T1:

Col1 Col2 Count

629 1465 9

629 3299 12

629 6633 1

629 - -

2546 1465 1

2546 4524 20

2546 7852 1

2546 - -

So the expected result is

Col1 Col2 Col3

629 3299 12

2546 4524 20

However I am not getting this result

In the load script I used this

T2:

LOAD Col1,
FirstSortedValue(Col2, -Col3) as NewCol
Resident T1
group by Col1
;

Actual Result:

Col1 Col2 Col3

629 -

2546 -

Below are the modified script and corresponding Result

T2:

LOAD Col1,
FirstSortedValue(Col2, Col3) as NewCol
Resident T1
group by Col1
;

Actual Result:

Col1 Col2 Col3

629 3299 12

2546 -

Question:

In A), what is that i am missing which is causing incorrect result

In B), the pattern observed is that the results are not displayed when the datarows same count for more than 1 row. In this case 2546 has tow rows with Count = 1. How to resolve this

Thanks for your time

Umashankar

Re: FirtSorted Value does not return expected value in some cases

Brett_Bleess — Wed, 22 Apr 2020 20:25:20 GMT

Best I can do for you is post the Help link to the function, so you can review that to ensure you have followed that documentation correctly:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/AggregationFunctions/firstsorted.htm

Here is a Design Blog post that make use of the function too that might further help:

https://community.qlik.com/t5/Qlik-Design-Blog/Additive-and-Non-Additive-Numbers/ba-p/1469714

Sorry I do not have something better for you.

Regards,
Brett

topic FirtSorted Value does not return expected value in some cases in QlikView

FirtSorted Value does not return expected value in some cases

Re: FirtSorted Value does not return expected value in some cases