topic Re: First sorted value() issue? in QlikView

First sorted value() issue?

Sun, 01 Mar 2015 06:33:55 GMT

Hi All,

I got table as below

Name runs wkts

a1 10 2

b2 20 1

c3 15 3

d4 10 3

How to show best performance (most wkts and least runs) by name, using firstsortedvalue().

Regards,

Janaki

Re: First sorted value() issue?

anbu1984 — Sun, 01 Mar 2015 07:20:08 GMT

vMinRuns=Min(runs)

='Players with least runs : ' &Concat(If(runs = vMinRuns, Name), ',')

vMaxWikcets=Max(wkts)

='Players took most wickets : ' & Concat(If(wkts = vMaxWikcets, Name), ',')

Re: First sorted value() issue?

maxgro — Sun, 01 Mar 2015 07:31:13 GMT

FirstSortedValue(TOTAL Name, -aggr(sum(wkts) - sum(runs)/sum(TOTAL runs),Name) )

Re: First sorted value() issue?

Sun, 01 Mar 2015 07:31:24 GMT

Hi Anbu,

I need to show out put like this in a text box.

Best Performance

d4 10/3

Re: First sorted value() issue?

lironbaram — Sun, 01 Mar 2015 07:31:48 GMT

hi your expression should look like this

=FirstSortedValue(Name & ' ' & Runs & '/' & Wkts,Runs/Wkts)

Re: First sorted value() issue?

anbu1984 — Sun, 01 Mar 2015 07:39:12 GMT

=Concat(If(runs = vMinRuns And wkts = vMaxWikcets, Name), ',') & vMinRuns & '/' & vMaxWikcets

Re: First sorted value() issue?

Sun, 01 Mar 2015 07:39:56 GMT

Hi Massimo,

What if we got repeated Values like.

Name runs wkts dt FSV

a 10 2 1-3-15

b 20 1 1-2-15

c 15 3 1-2-15

d 10 3 2-215

e 7 3 1-2-15

f 7 3 2-2-15

who first scored has to come first.please help me on this

Re: First sorted value() issue?

anbu1984 — Sun, 01 Mar 2015 07:44:55 GMT

=Concat(If(runs = Min(TOTAL runs) And wkts = Max(TOTAL wkts), Name), ',') & ' ' & Min(TOTAL runs) & '/' & Max(TOTAL wkts)

If you want to pick player who scored first, try this

=SubField(Concat(If(runs = Min(TOTAL runs) And wkts = Max(TOTAL wkts), Name ), ',',Aggr(Max(dtFSV),Name)),',',1) & ' ' & Min(TOTAL runs) & '/' & Max(TOTAL wkts)

Re: First sorted value() issue?

lironbaram — Sun, 01 Mar 2015 07:46:16 GMT

hi then you can use

=FirstSortedValue(Name & ' ' & Runs & '/' & Wkts,Runs/Wkts+date)

remember date is a number

Re: First sorted value() issue?

maxgro — Sun, 01 Mar 2015 08:03:27 GMT

this should works

FirstSortedValue(TOTAL Name, -aggr(sum(wkts) - sum(runs)/sum(TOTAL runs),Name) +dt/ max(TOTAL dt) / 10000 )

but I don't like it very much

Re: First sorted value() issue?

Sun, 01 Mar 2015 08:47:46 GMT

Hi Anbu,

What is dtFSV

Re: First sorted value() issue?

anbu1984 — Sun, 01 Mar 2015 08:56:34 GMT

Load Name,runs,wkts,Date#(dtFSV,'DD-MM-YY') As dtFSV Inline [

Name,runs,wkts,dtFSV

a,10,2, 1-3-15

b, 20,1, 1-2-15

c, 15,3, 1-2-15

d, 10,3, 2-2-15

e, 7,3, 1-2-15

f, 7,3, 2-2-15];