FirtSorted Value does not return expected value in some cases

[umashankarus]

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

A)

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   -                         

B)

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

[Brett_Bleess]

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/Aggre...

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