First occurrence by sorting

[Anonymous]

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

[Anil_Babu_Samineni]

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

[tresesco]

In the script ?

[Anonymous]

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

[Anonymous]

Yes should be done in the script..

[tresesco]

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;

[Anil_Babu_Samineni]

May be FirstSortedValue() for status in script?

[Anonymous]

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]

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

[Anonymous]

But I need to sort by these two fields..