Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

First occurrence by sorting

Hi All,

Is it possible to sort the values and get the first occurrence in Qlikview?

My requirement is this. I have to sort the names and one more column called Status in desc and get the first occurrence. Is this possible?

   

 

QNUmNameStatus
1000ABCYes
1000ABCNo
12000XYZNo
12000XYZNo
12000XYZYes
200DEFN/A
200DEFYes
100IJKYes
10KLMNo

From this table i should get the following result:

   

QNUmNameStatus
1000ABCYes
12000XYZYes
200DEFYes
100IJKYes
10KLMNo

Is this possible?

Thanks,

Anupama Jagan

10 Replies
Anil_Babu_Samineni

When you talk about Sorting? Why Status "No" is not available in the result?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
tresesco
MVP
MVP

In the script ?

Anonymous
Not applicable
Author

Because for the QNum 10 and name KLM we have only one status - No..so that should be in the result..

Anonymous
Not applicable
Author

Yes should be done in the script..

tresesco
MVP
MVP

Try like:

t1:

Load * Inline [

QNUm, Name, Status

1000, ABC, Yes

1000, ABC, No

12000, XYZ, No

12000, XYZ, No

12000, XYZ, Yes

200, DEF, N/A

200, DEF, Yes

100, IJK, Yes

10, KLM, No]

;

NoConcatenate

t2:

Load

  QNUm,

  Name,

  FirstValue(Status) as Status

Resident t1 group by QNUm, Name Order By Name asc, Status desc ;

Drop Table t1;

Capture.PNG

Anil_Babu_Samineni

May be FirstSortedValue() for status in script?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

Hi Tresesco,

I have a doubt in this..if i have more columns in the table what should be done?

Like i have few other fields like PNums, Dates etc should i add those to in group by?

tresesco
MVP
MVP

Yes, may be both in group By and Order By.

Anonymous
Not applicable
Author

But I need to sort by these two fields..