topic Re: About FirstSortedValue in QlikView

About FirstSortedValue

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

Hi,

I have data as below

sample:

LOAD * INLINE

[ NO, name, status

1,'Bala',5

2,'Bala',4

3,'Bala',5

4,'Suresh',5

5,'Suresh',4

6,'Suresh',5

];

I need out put as follows

I used below code:

final:

LOAD FirstSortedValue( DISTINCT NO,-status) AS NO Group by name;

LOAD * Resident sample order by NO desc;

is this the best way ? or suggest if any other that this ?

Regards,

Balanandam

Re: About FirstSortedValue

vishsaggi — Sun, 29 Oct 2017 18:07:53 GMT

You cant use DISTINCT in your firstsortedvalue. Try like below:

Max:

LOAD * INLINE

[ NO, name, status

1,'Bala',5

2,'Bala',4

3,'Bala',5

4,'Suresh',5

5,'Suresh',4

6,'Suresh',5

];

INNER JOIN

LOAD name,

FirstSortedValue(NO, -NO) AS NO,

Max(NO) AS MaxNum

Resident Max

Group By name;

Re: About FirstSortedValue

sunny_talwar — Sun, 29 Oct 2017 20:17:55 GMT

Since you have two NO with the same max status... which of the two would you like to pick?

Re: About FirstSortedValue

effinty2112 — Mon, 30 Oct 2017 09:26:04 GMT

Hi,

Try

name	Max(Aggr(If(status = max(TOTAL<name> status),NO),name,NO))
Bala	3
Suresh	6

This will return the highest value of NO if there is a tie.

Cheers

Andrew

Re: About FirstSortedValue

trdandamudi — Mon, 30 Oct 2017 13:10:39 GMT

One more possible way to do this:

Source_Data:
LOAD * INLINE
[ NO,Name, Status
1,'Bala',5
2,'Bala',4
3,'Bala',5
4,'Suresh',5
5,'Suresh',4
6,'Suresh',5
];

NoConcatenate
Temp:
Load Max(NO) as Number
Resident Source_Data
Group by Name;

NoConcatenate
Final:
Load NO, Name, Status
Resident Source_Data
Where exists(Number,NO);

Drop Table Source_Data,Temp;

Re: About FirstSortedValue

balanandam — Mon, 30 Oct 2017 16:50:14 GMT

I need highest status(possible values: 1,2,3,4,5) at Max NO.

Re: About FirstSortedValue

sunny_talwar — Mon, 30 Oct 2017 16:56:49 GMT

You want to pick status at highest NO? May be this

LOAD Max(NO) as MaxNo,

name,

FirstSortedValue(status, -NO) as MaxNoStatus

Resident ...

Group By name;