Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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