topic Re: First occurrence by sorting in QlikView

First occurrence by sorting

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

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?

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

From this table i should get the following result:

QNUm	Name	Status
1000	ABC	Yes
12000	XYZ	Yes
200	DEF	Yes
100	IJK	Yes
10	KLM	No

Is this possible?

Thanks,

Anupama Jagan

Re: First occurrence by sorting

Anil_Babu_Samineni — Fri, 23 Jun 2017 06:12:48 GMT

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

Re: First occurrence by sorting

tresB — Fri, 23 Jun 2017 06:15:24 GMT

In the script ?

Re: First occurrence by sorting

Anonymous — Fri, 23 Jun 2017 06:16:03 GMT

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

Re: First occurrence by sorting

Anonymous — Fri, 23 Jun 2017 06:16:27 GMT

Yes should be done in the script..

Re: First occurrence by sorting

tresB — Fri, 23 Jun 2017 06:24:21 GMT

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;

Re: First occurrence by sorting

Anil_Babu_Samineni — Fri, 23 Jun 2017 06:26:13 GMT

May be FirstSortedValue() for status in script?

Re: First occurrence by sorting

Anonymous — Fri, 23 Jun 2017 06:46:35 GMT

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?

Re: First occurrence by sorting

tresB — Fri, 23 Jun 2017 07:02:39 GMT

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

Re: First occurrence by sorting

Anonymous — Fri, 23 Jun 2017 07:04:07 GMT

But I need to sort by these two fields..

Re: First occurrence by sorting

Anonymous — Fri, 23 Jun 2017 08:45:03 GMT

I will rephrase my question:

I have few fields in my report and when i recently added one field we are getting multiple rows for the same name.

In order to eliminate the duplicates we thought of sorting by the name asc and status desc and getting the first occurrence.

We have more than 20 to 30 fields in the table as of now.

Kindly let me know how to go about this.

Thanks,

Anupama Jagan